Get a single record for each user

Meax

I have the following SQL table, ID LogDate Name Department ----------------------------------------------- 1 3/26/2015 7:55 AM Adam HR 2 3/26/2015 7:53 AM Tony Fin 3 3/26/2015 7:59 AM Mark Mang 1 3/26/2015 8:15 AM Adam HR 3 3/26/2015 8:10 AM Mark Mang 2 3/26/2015 7:53 AM Tony Fin i like to get this output ID LogDate Name Department ------------------------------------------------- 2 3/26/2015 7:53 AM Tony Fin 1 3/26/2015 7:55 AM Adam HR 3 3/26/2015 7:59 AM Mark Mang i want to get a single record for each user ordered by min LogDate. my table name is "DeviceLogs"

Afzaal Ahmad Zeeshan

A simple solution would be,

SELECT TOP 1 * FROM DeviceLogs ORDER BY LogDate;

Which will select the top 1 record from your records, ordering them by your column containing LogData, you can add or remove DESC at the end of the ORDER BY clause if it returns the opposite side and so on.

The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~

Meax

then is selects just a single record. i want records of all users to be selected but without repeating. if userid 1 has more than one log then just select earliest record for user id 1 and ignore other records for him.

Afzaal Ahmad Zeeshan

Then you should group your records using GROUP BY clause over your LogTime column.

The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~

Meax

SELECT * FROM DeviceLogs GROUP BY LogDate this gives me error "because it is not contained in either an aggregate function or the GROUP BY clause"

RUs123

Well you select all columns and group by LogDate. Group by makes a single row out of all the rows that have the same LogDate in this example. But what information is it supposed to put in ? That's what aggregate functions are for. However I think you should group by name, where LogDate=the day you want to select by. You could also group by LogDate and name. Anything that isn't in group by must be in an aggregate function. So you have to decide which time you want to have selected (from what I deducted, first log ? ). A bit unrelated to your question, but why do you have ID's that repeat ?

Simon_Whale

Are your Logdates varchars?

Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON

Corporal Agarn

Would this help?

SELECT ID, MIN(LogDate) AS LogDate, Name, Department
FROM yourtable
GROUP BY ID, Name, Department;

Mongo: Mongo only pawn... in game of life.

User 11524996

SELECT d.ID, d.LogDate, d.Name, d.Department
FROM DeviceLogs d, (SELECT MIN(LogDate) FROM DeviceLogs GROUP BY Name) s
WHERE s.MIN(LogDate) = d.LogDate
ORDER BY d.LogDate;

SELECT ID, MIN(LogDate) AS LogDate, Name, Department
FROM yourtable
GROUP BY ID, Name, Department;

Meax

thank you all