Debugging a SQL Query

Question

I have a table structure like below. I need to select the row where User_Id =100 and User_sub_id = 1 and time_used = minimum of all and where Timestamp the highest. The output of my query should result in :

US;1365510103204;NY;1365510103;100;1;678;

My query looks like this.

select * 
from my_table 
where CODE='DE' 
  and User_Id = 100 
  and User_sub_id = 1 
 and time_used = (select min(time_used) 
                  from my_table 
                  where CODE='DE' 
                    and User_Id=100 
                    and User_sub_id= 1);

this returns me all the 4 rows. I need only 1, the one with highest timestamp. Many Thanks

CODE:   Timestamp:  Location:   Time_recorded:  User_Id:    User_sub_Id:    time_used
"US;1365510102420;NY;1365510102;100;1;1078;
"US;1365510102719;NY;1365510102;100;1;978;
"US;1365510103204;NY;1365510103;100;1;878;
"US;1365510102232;NY;1365510102;100;1;678;
"US;1365510102420;NY;1365510102;100;1;678;
"US;1365510102719;NY;1365510102;100;1;678;
"US;1365510103204;NY;1365510103;100;1;678;
"US;1365510102420;NY;1365510102;101;1;678;
"US;1365510102719;NY;1365510102;101;1;638;
"US;1365510103204;NY;1365510103;101;1;638;

Which DBMS are you using? Postgres? Oracle?

user330315
– user330315

2013-05-03 12:21:09 +00:00
Commented May 3, 2013 at 12:21 — user330315
– user330315, Commented May 3, 2013 at 12:21
@a_horse_with_no_name. Postgres

java_learner
– java_learner

2013-05-03 12:24:07 +00:00
Commented May 3, 2013 at 12:24 — java_learner
– java_learner, Commented May 3, 2013 at 12:24

user330315 · Accepted Answer · 2013-05-03 12:37:33Z

2

Another possibly faster solution is using window functions:

select * 
from (
  select code,
         timestamp,
         min(time_used) over (partition by user_id, user_sub_id) as min_used,
         row_number() over (partition by user_id, user_sub_id order by timestamp desc) as rn,
         time_used,
         user_id, 
         user_sub_id
  from my_table 
  where CODE='US' 
    and User_Id = 100 
    and User_sub_id = 1 
) t
where time_used = min_used
  and rn = 1;

This only needs to scan the table once instead of twice as your solution with the sub-select is doing.

I would strongly recommend to rename the column timestamp.

First this is a reserved word and using them is not recommended.

And secondly it doesn't document anything - it's horrible name as such. time_used is much better and you should find something similar for timestamp. Is that the "recording time", the "expiration time", the "due time" or something completely different?

answered May 3, 2013 at 12:37

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

java_learner Over a year ago

thanks for the answer and as you pointed out, the column is actually not named as 'timestamp',it is called 'calculation_begin_timestamp' as it was too long, I just used 'timestamp'.

Matteo Tassinari · Accepted Answer · 2013-05-03 12:29:38Z

1

Then try this:

select *
from my_table
where CODE='DE'
  and User_Id=100
  and User_sub_id=1
  and time_used=(
    select min(time_used)
    from my_table
    where CODE='DE'
    and User_Id=100 and User_sub_id=1
  )
order by "timestamp" desc --   <-- this adds sorting
limit 1; --   <-- this retrieves only one row

edited May 3, 2013 at 12:29

user330315

answered May 3, 2013 at 12:16

Matteo Tassinari

18.6k8 gold badges65 silver badges85 bronze badges

3 Comments

user330315 Over a year ago

You don't necessarily need a group by when using an aggregate function.

Matteo Tassinari Over a year ago

@a_horse_with_no_name: thanks for the info, I thought it was required.

Matteo Tassinari Over a year ago

@a_horse_with_no_name: thanks for the correction, I never used Postgres but only mysql, and always used ` for column names escape.

mguimard · Accepted Answer · 2013-05-03 12:18:23Z

0

Add to your query the following condition

ORDER BY Timestamp DESC, LIMIT 1

answered May 3, 2013 at 12:18

mguimard

1,89913 silver badges14 bronze badges

Collectives™ on Stack Overflow

Debugging a SQL Query

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related