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`)

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

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 |

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

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

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.

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

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

try this select * from table1 group by account_id demo here this will give you the first of every account_id

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)

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

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;

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

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.

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

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:

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.

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

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;

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.

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.

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

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

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).

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

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

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

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

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

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.

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;

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)

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

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)

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

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

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?

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.

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

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;