w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
MySQL: How to fetch all columns with distinct clause on one column with latest created record
Limit can be used in mysql to achieve this. SELECT DISTINCT deviceID, createdDate FROM `devicePosition` ORDER BY createdDate desc limit 1 Also, you can where clause SELECT DISTINCT deviceID, createdDate FROM `devicePosition` WHERE createdDate = (select max(createdDate) from `devicePosition`)

Categories : Mysql

to select latest date and last but-one date from date column in SQL Server 2005
Simple use of ROW_NUMBER: ;WITH OrderedRows as ( SELECT ID,Date,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date desc) rn from Table ) select * from OrderedRows where rn <=2

Categories : SQL

Mysql Select distinct records from latest dates only
Try this query If you want for pnly user1 then use this query select username, course_id, max(ldate) as date from tbl where username='user1' group by course_id SQL FIDDLE | USERNAME | COURSE_ID | DATE | ------------------------------------- | user1 | 22 | 2013-06-03 | | user1 | 54 | 2013-06-03 | If you want to find latest date for all the user then use this query select username, course_id, max(ldate) as date from tbl group by username, course_id In this query data of user2 will also be included | USERNAME | COURSE_ID | DATE | ------------------------------------- | user1 | 22 | 2013-06-03 | | user1 | 54 | 2013-06-03 | | user2 | 71 | 2013-06-04 |

Categories : Mysql

Query on how to Select Date before the latest Date in MySQL
have you tried anything? select yourDate from yourTable order by str_to_date(yourDate, '%Y/%m/%d') desc limit 2, 1 if you want to avoid duplicates, add group by yourDate clause before the order, and limit will be 1,1 instead of 2,1 edit As the field is a varchar, and you want to order as a date and not as a varchar, you need to cast the field in the order by

Categories : Mysql

Select values with latest date and group by an other column
Add an index on (uid,date): ALTER TABLE network ADD INDEX uid_date( uid, date ) and try something like: SELECT n.id, n.uid, greatest( n.right_leg, n.left_leg ) as max_leg, n.date FROM ( SELECT uid,max(date) as latest FROM network WHERE right_leg>=500 OR left_leg >=500 GROUP BY uid ) ng JOIN network n ON n.uid = ng.uid AND n.date = ng.latest

Categories : Mysql

MYSQL SELECT DISTINCT value from and column and its corresponding value from another
You can use a GROUP BY query to reduce the result to one row per distinct category: SELECT category, path FROM galleryinfo GROUP BY category The problem with this is that it's arbitrary which path this query returns. In fact, in most SQL implementations, this is an illegal query and results in an error. MySQL is more lenient, but it will return an arbitrary path from those matching the category (in practice, it chooses the path from the first row with respect to physical storage, but this is not documented or guaranteed). You can choose either the MIN(path) or MAX(path), or concatenate all paths per category together into one string with GROUP_CONCAT(path), but that's about it.

Categories : Mysql

DISTINCT clause on string column in mysql
You are not getting a duplicate "001 Towing", the distinct is applied to each group generated by the group by clause. Therefore, you get 4 x "001 Towing" one for each "Sp_Id"; if you remove the group by, you will get only one "001 Towing". You made the wrong question to the database, and the database gave you the right answer to your wrong question. If you tell us what the query is supposed to do, we might be able to help you fix it... for your new specification try this query, it should do the trick: SELECT Sp_Name as Sp_Name, CONCAT('SP_', **group_concat ( Sp_Id separator '_ ' )** ) as searchPayerId FROM ServiceProviders WHERE Comp_Id= 3 AND Sp_Id NOT IN (1,2) AND Sp_Name != '' **GROUP BY Sp_Name** BTW you can look in the mySQL manual how the Group By works

Categories : Mysql

mysql query group by where column distinct
It should look something like this SELECT COUNT(DISTINCT e.IP), count(e.id), count(c.id), e.Nid FROM tbl1 e LEFT JOIN tbl2 c ON e.Uid = c.Uid where e.at > '".$from."' AND e.at < '".$to."' GROUP BY e.Nid

Categories : Mysql

MySQL - distinct on only one column - only display FIRST duplicate row
try this select * from table1 group by account_id demo here this will give you the first of every account_id

Categories : PHP

mysql select distinct date takes FOREVER on database w/ 374 million rows
When you have a composite index on 2 columns, like your (symbol, date) primary key, searching and grouping by a prefix of they key will be fast. But searching for something that doesn't include the first column in the index requires scanning all rows or using some other index. You can either change your primary key to (date, symbol) if you don't usually need to search for symbol without date. Or you can add an additional index on date: alter table minute_data add index (date)

Categories : Mysql

MySQL query that returns distinct count of records against one colum while using a second column as the filter
There are a few ways to handle it. Perhaps the easiest is via a NOT IN () subquery: SELECT COUNT(DISTINCT sessionId) FROM transcode WHERE sessionId NOT IN (SELECT sessionId FROM transcode WHERE transcode = 103) Example: http://sqlfiddle.com/#!2/14bda/3 It can also be done via a LEFT JOIN looking for NULL on the right side table. This may be more efficient than the NOT IN () on larger tables. SELECT COUNT(DISTINCT t.sessionId) FROM transcode t /* LEFT JOIN against a subquery returning only sessionId with a 103 transcode */ LEFT JOIN ( SELECT sessionId FROM transcode WHERE transcode = 103 ) texclude ON t.sessionId = texclude.sessionId WHERE /* and retrieve only those where these is *no match* on the joined subquery */ texclude.sessionId IS NULL Example: http://s

Categories : Mysql

Select datetimepicker date as per mysql table column date (c#)
you need to get membership date value from the datatable and set it as selected date of datetimepicker DateTime membershipdate = YourDataTable.Row[0].Field<DateTime>("MEMBERSHIP_DATE"); datetimepicker.Value= membershipdate;

Categories : C#

MySQL select date range from table where there's only one column for date
SELECT emp_name,min(startdate) as startdate,max(ldate) as enddate,team FROM ( SELECT emp_name,startdate ,team, @n:=if(@lastteam=team,@n,@n+1) rank, @l:=if(@enddate=startdate,startdate,@enddate) ldate, @lastteam:=team, @enddate:=startdate FROM t, (SELECT @n := 0,@lastteam:='',@enddate:='') n ORDER BY startdate desc ) m GROUP BY rank SQL FIDDLE here: http://www.sqlfiddle.com/#!2/860fd/20

Categories : Mysql

Auto update status column based on due date column in mysql database
UPDATE `users` SET `status`='Overdue' WHERE `due_date` > CURDATE(); You can use this query directly in phpMyAdmin if you want to update it once. Or you can write a cron that runs once every day and updates the records.

Categories : Mysql

SQL Insert rows into table that must have 2 distinct columns but also one non distinct column
You'll have to just replace the PRIZEID value with new ones. Because it sounds like you currently have duplicates on your PROMOTION table First add all the distinct PRIZENAMEs and COSTs to your new PRIZE table: INSERT INTO prize(PRIZEID, COST, PRIZENAME) SELECT DISTINCT (SELECT MAX(PRIZEID)+1 FROM PRIZE), r.COST, r.PRIZENAME FROM PROMOTION r; Then update your PROMOTIONs table with the new PRIZEID UPDATE PROMOTION R SET R.PRIZEID = (SELECT P.PRIZEID FROM PRIZE WHERE P.PRIZENAME=R.PRIZENAME AND P.COST=R.COST); Then, I think from there you can safely delete the columns from your PROMOTIONs table

Categories : SQL

Distinct value of a column and do a count on each distinct value
Considering the following example: To get the unique values, and the occurrences of each unique value, you can do the following: The image is tiny, so here is are the formulas to type-in and drag down: FORMULAS 1. Type in the first cell of column B: =IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$8),0,0),0))),"",INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$8),0,0),0))) Drag the formula down the column B. You will get a list of unique values. 2. Type in the first cell of column C: =COUNTIF($A$2:$A$8,B2) Drag the formula down the column C. You will get the number of occurrences of each unique value. RESULT This is what you will get:

Categories : Excel

MySQL - Date Column Format
you can't change the format during table creating table, you can change the format of date for displaying user by using you programming logic like if you are using PHP as your server site language the you can convert it your desired format.

Categories : Mysql

How to convert a mysql datetime column to just date?
You can use the date_format function for mysql select * from alerts_to_send where date_format(date_to_send, "%m/%d/%Y") = date_format(now(), "%m/%d/%Y") Here is reference to date_format: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Categories : Mysql

How to return a column value basted on last activity date VIA MySQL
You can do this using order by, without the max(): SELECT completed_on, completed_by, completed_on AS completed_on FROM activity WHERE activity_code = 5 ORDER BY completed_on desc LIMIT 1;

Categories : Mysql

JavaScript (Node.js) -- retrieving date from MySQL column is a day behind
Unless it was posted a long time ago, I had the same problem. I have detected the problem: when removing the time part in the datetime, hour was automatically set to "00:00:00" by MySQL. Due to UTC time was not set properly, it was substracting one hour, so it was 23:00:00 from previous day in my case. Removing again the time part, that resulted in a day before. It is necessary to set timezone in MySQL connection.

Categories : Javascript

MySQL Return all unique values in a column along with the last date associated with each
This is a basic aggregation query: SELECT `ID`, min(`Date`), max(`date`) FROM `relative`.`datatable` GROUP BY `ID`; And, as @rogoas points out, your query is not guaranteed to return the minimum date. It will return an arbitrary date for each id.

Categories : Mysql

MySQL Group by week num w/ multiple date column
Try: SELECT WeekInYear, ForecastCount, ActualCount FROM ( SELECT A.WeekInYear, A.ForecastCount, B.ActualCount FROM ( SELECT weekofyear(forecast_date) as WeekInYear, COUNT(forecast_date) as ForecastCount, 0 as ActualCount FROM TableWeeks GROUP BY weekofyear(forecast_date) ) A INNER JOIN ( SELECT * FROM ( SELECT weekofyear(forecast_date) as WeekInYear, 0 as ForecastCount, COUNT(actual_date) as ActualCount FROM TableWeeks GROUP BY weekofyear(actual_date) ) ActualTable ) B ON A.WeekInYear = B.WeekInYear) AllTable GROUP BY WeekInYear; Here's my Fiddle Demo

Categories : Mysql

Join Distinct Id on non-distinct id (MySql)
SELECT t.ID, t.val_string, t.val_int, t.val_datetime FROM table1 AS t LEFT JOIN (subquery) AS v_table ON t.ID = v_table.ID Sample fiddle

Categories : Mysql

MySQL index on timestamp column not used for large date ranges
The MySQL optimizer tries to do the fastest thing - where it thinks that using the index will take as long or longer than doing a table scan, it abandons the available index. This is what you see it doing in your examples: where the range is small (1 day) the index will be faster; where the range is large, you're going to be hitting so much more of the table you might as well scan the table directly (remember, using the index involves searching the index and then grabbing the indexed records from the table - two sets of seeks). If you think you know better than the optimizer (it isn't perfect), use hints (http://dev.mysql.com/doc/refman/5.5/en/index-hints.html).

Categories : Mysql

MySQL: Growth in Column Value for Given Foreign Key and Specific Date Range
If the pins column is a daily delta, the net change in the number of pins added/removed on a given date, then: SELECT @StartDate = DATE_FORMAT(NOW(),'%Y-%m-%d') + INTERVAL -1 DAY SELECT @EndDate = DATE_FORMAT(NOW(),'%Y-%m-%d') SELECT t.page_id , SUM(t.pins) AS `Pins_DayOverDay` FROM mytable t WHERE t.date >= @StartDate AND t.date <= @EndDate GROUP BY t.page_id ORDER BY `Pins_DayOverDay` DESC LIMIT 1 If the pins column is cumulative and contains a total count of the number of effective pins (pins done on previous days plus pins added/removed today), assuming that (page_id,date) is unique: SELECT t.page_id , (t.pins - s.pins) AS `Pins_DayOverDay` FROM mytable t JOIN mytable s ON s.page_id = t.page_id WHERE t.date = @EndDate AND s.date = @Start

Categories : Mysql

PHP date with 30 mins added, is reversed when inserted into MySQL datetime column
All date and time functions are now dependent upon a correct timezone setting. You can either do this in your script date_default_timezone_set('America/Los_Angeles'); // for example OR Check your php.ini for this setting date.timezone = UTC And set it correctly for your specific timezone, here is a List of supported timezones which you will need either way

Categories : PHP

SUM over column based on distinct row groups selected for another column
Use a correlated subquery: select count(distinct SAMPNAME), (select sum(ss) from (select distinct SAMPNAME, SAMPSIZE as ss from TEST_TABLE as T2 where T2.TC_ID = T1.TC_ID) ), TC_ID from TEST_TABLE as T1 group by TC_ID

Categories : SQL

Excel VBA - How to compare today's date in one column with a future date in a second column
First of all, you need to understand how does VBA "understands" a date. Excel, Access, VBA and many MS products store dates as a double precision number; the integer part of the number is the date (number of days counted since January 1st, 1900), and the decimal portion of the number is the time (fraction of day). This makes it easy to compare two dates: You can compare, add or substract dates just as if you were using numbers. So, if t0 and t1 are two dates (t1 >= t0), then the expression t1 - t0 will give you the difference of days. Now... how yo count the "business days" between two dates? The format() function in VBA can help you. You can use this function to return the "day-of-week" number. Check the online help of the function: http://msdn.microsoft.com/en-us/library/office/gg2

Categories : Excel

ERROR 2003 (HY000): Can't connect to MySQL server on 'hostname' (111)
Simply check your my.cnf and change from bind-address = 0.0.0.0 to bind-address = 127.0.0.1 if you don't have that parameter just add it. Binding to the 0.0.0.0 let your mysql being available on every IP configured cause you can't bind just on two or three IP on the server, the config can be: localhost or everything. Then check your /etc/hosts file and be sure that the line 127.0.0.1 localhost contains also your server hostname, as example: my hostname is "db01", my /etc/hosts is 127.0.0.1 localhost db01 Keep in mind that after the install process via yum (I don't know if CentOS do this automagically for you, I just know that Gentoo does not) you have to execute mysql_install_db and then configure the password for the root user, be absolutely sure that you'd set up a password

Categories : Mysql

Select Distinct Column For Each Value In Another Column Then Group By
Is this what you are looking for: SELECT MIN(id), number, package_id, date FROM MyTable GROUP by number, package_id, date It certainly satisfies your expected result set.

Categories : Mysql

Get the latest value on each date
I would do this with a correlated subquery: select t1.*, (select top 1 value from @table2 t2 where t2.idColumn = t1.idColumn and t2.dateColumn <= t1.dateColumn order by t2.dateColumn desc ) t2value from @table1 t1;

Categories : SQL

Updating only ID's with the latest date SQL (2 of 6)
Just add another condition to where: Update Table 1 SET DY_H_ID = ( SELECT MAX(ID) FROM Table 2 WHERE H_DateTime <= DY_Date AND H_IDX = DY_IDX AND H_HA_ID = 7 AND H_HSA_ID = 19 AND H_Description LIKE 'Diary item added for :%' ) WHERE DY_H_ID IS NULL AND DY_IDX IS NOT NULL and DY_Date = (select max(DY_Date) from Table 1)

Categories : SQL

LINQ to SQL Get Latest Date from Object
I'm not sure how you feel about Lambda expressions but I would probably do this: db.Users .Join(db.Adjusters, u => u.Id, a => a.UserId, (u, a) => new { User = u, Adjuster = a }) .Join(db.AdminAdjusterStatus, a => a.Adjuster.Id, s => s.AdjusterId, (a, s) => new { User = a.User, Adjuster = a.Adjuster, AdminAdjusterStatus = s }) .Where(x => x.User.userType == "adjuster" && x.AdminAdjusterStatus.status == "approved" && x.AdminAdjusterStatus.statusDate == db.AdminAdjusterStatus .Where(y => y.AdjusterId == x.AdminAdjusterStatus.AdjusterId) .Max(z => z.statusDate)) .Select(a => new

Categories : C#

Selecting only rows with the latest date
try this: SELECT * FROM contacts c1 WHERE NOT EXISTS( SELECT 'NEXT' FROM contacts c2 WHERE c1.fk_parent = c2.fk_parent AND c2.date > c1.date) For next time, add your tables (with field and relations). My sample is very generic because I don't know your structure fk_parent, I assume, is a field representing a foreign key, where, two different contacts are linked (for example that field can be a customer field)

Categories : SQL

How to move the HEAD to the latest date in git?
When you called git checkout 123456 you moved your HEAD from the commit you were currently on (most likely the head of the master branch) to the commit 123456. Therefor you are looking for a way to move HEAD back to the branch you were previously on, which you can do with: git checkout master If you want to take a look at a specific revision of a file, you can either just view it using git show 123456:/txt/file.txt or temporarily check only this file out with git checkout 123456:/txt/file.txt // use it git checkout :/txt/file.txt Explanation of your tries: git reset --hard Reverts all changes of the current HEAD, but does not move HEAD. After a reset, git status shows that everything is "clean". git clean Removes all untracked files from the working tree, again HEAD is not mov

Categories : GIT

Sorting from earliest to latest date
you could use something like this: List<stat> statList = new List<stat>(); ... var selectedItem = statList .OrderBy(item => item.date) .Select(l => l.last()); or you could use OrderByDecending() instead

Categories : C#

Select one value of column, corresponding with distinct value of other column
In your desired output, the ordertouserId = 2 shows the Total_value for voucherId = 3. How did you determine that you don't want to show the Total_value for voucherId = 2? Sorry, that's not an answer, but I don't have enough "Reputation" to post a comment on the question. Baring a better understanding of how you selected Total_value = 500 (rather than Total_value = 300) for ordertouserId = 2, my answer would have to be: The output you desire is not possible to obtain because there is no clear way to determine which voucherId should be used in calculating Total_value where ordertouserId = 2. BTW, I think there's an error in the first output table which shows ordertouserId = 2 with Total_value = 1000. Shouldn't the TotalValue for ordertouserId = 2 be 300 + 500 = 800?

Categories : Mysql

XPATH select condition with the latest date
If I understand what you want correctly, you cannot do this all in XPath v1 due to the lack of a maximum function. So get all the STATUS elements with the desired TYPE: /STATUSLIST/STATUS[TYPE/@VALUE=2] and then sort or maximum function in the calling environment.

Categories : Xml

How can I select the record with the latest date in each group
this should do the job select `id` , `pid` , `end` from table1 where `end` in (select max(`end`) from table1) DEMO HERE DUE to your suggest try this edit : select `id` , `pid` , `end` from ( select max(id) id ,`pid` ,max(`end`) as `end` from table1 group by `pid`)t DEMO HERE

Categories : Mysql

Selecting multiple values of other column basing on distinct values of column on same table
You can use the LISTAGG function as of Oracle 11g R2. e.g. SELECT deptno , LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno;

Categories : SQL



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.