SQL select only one item which meets condition

Question

I have a very narrow table: DATA, ID, LAT, LNG, TIME. (https://gyazo.com/52b268c00963ed12ba85c6765f40bf63)

And I want to select the newest data for each different ID. I was using query like

SELECT * 
  FROM name_of_table 
 WHERE TIME > my_given_time;

but it selects TOTALLY all datas and not only data for each different id which meets the condition.

Could somebody please help me write the query?

Thank you for any help. :)

EDIT

The final look of my working query looks like:

SELECT * FROM (SELECT * FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TIME DESC) AS ROWNUMBER, * FROM my_table) WHERE ROWNUMBER = 1) WHERE TIME > my_time;

Thanks everyone for help

Which RDBMS are you using? – Strawberry Apr 25 '17 at 16:39 — Strawberry, Apr 25 '17 at 16:39
@Strawberry I use DashDB on Bluemix – WutchZone Apr 25 '17 at 17:08 — WutchZone, Apr 25 '17 at 17:08

score 4 · Accepted Answer · answered Apr 25 '17 at 18:28

4

How about something like this

SELECT ID, DATA 
FROM (
 SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TIME DESC) AS ROWNUMBER, ID, DATA
 FROM name_of_table)
WHERE ROWNUMBER = 1;

For these dummy records

INSERT INTO name_of_table (ID, TIME, DATA) VALUES('test',1230,16);
INSERT INTO name_of_table (ID, TIME, DATA) VALUES('test2',1235,10);
INSERT INTO name_of_table (ID, TIME, DATA) VALUES('test',1234,20);

the query returns the data value for the largest timestamp for each ID

  ID    DATA
  ----- ----
  test    20
  test2   10

answered Apr 25 '17 at 18:28

ptitzler

923
4
8

It almost works as i wanted! I just little modified your query (viz. edit). Thanks <3 – WutchZone Apr 25 '17 at 20:23
1

Glad it helped you find a solution that works for you! Note that there's really no need for your outermost SELECT to filter by time. Consider changing the condition "WHERE ROWNUMBER = 1" to "WHERE ROWNUMBER = 1 AND TIME > my_given_time" instead to keep it simple. – ptitzler Apr 26 '17 at 05:59

score 0 · Answer 2 · edited Apr 25 '17 at 16:44

0

Try this:

SELECT a.*
FROM `sometable` a
JOIN (SELECT MAX(TIME) AS `max_time`,`ID` FROM `sometable` group by `ID`) b
ON b.`ID` = a.`ID` AND a.`TIME` = b.`max_time`;

edited Apr 25 '17 at 16:44

Strawberry

33,750
13
40
57

answered Apr 25 '17 at 16:04

Sloan Thrasher

4,953
3
22
40

@SloanThrasher I don't know if i am writing this wrong. So everything in ' ' i should replace with my own "variables"? – WutchZone Apr 25 '17 at 17:12
Not variables, but column and table names for the back tics, – Sloan Thrasher Apr 25 '17 at 17:30

score 0 · Answer 3 · answered Apr 25 '17 at 16:16

0

I would do something like this:

SELECT tt.* FROM tblTest tt
WHERE _ID IN (
    SELECT TOP 1 _ID FROM tblTest WHERE ID = tt.ID ORDER BY TIME DESC
)

Assuming _ID that i found in your linked picture is a unique identifier for each individual row

answered Apr 25 '17 at 16:16

UberGrunk

63
8

I copied it and by DB threw an error : **Error occurred processing a conditional compilation directive near "_". Reason code="7". SQLSTATE=428HV** – WutchZone Apr 25 '17 at 17:16

SQL select only one item which meets condition

3 Answers3