asked on

Grabbing latest 4 records of same number with SQL

Guy Hengel [angelIII / a3]

Checking the PostGreSQL documentation, as it has ROW_NUMBER() window function, you can use the syntax specified in this article to do this:
https://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html

select *
  from ( select CUSTOMER_NAME,  TIMESTAMP          
                 , ROW_NUMBER() OVER ( PARTITION BY CUSTOMER_NAME ORDER BY  TIMESTAMP          DESC ) rn
            FROM yourtable
 ) sq
where sq.rn <= 3

Open in new window

a index on customer_name + timestamp should make sure you get the best performance.

hi4ppl

ASKER

Hi,

I have one table so there is no join involved here all data is in one flat table, anyway this is possible by query or procedure language.

thanks for help

Guy Hengel [angelIII / a3]

my query example should do this, there is no join involved.
the article is indeed about "joins", but that's only the usual case when you have "duplicates".

hi4ppl

ASKER

Well when I run this I get zero result but yeah I have added data as well but it's not giving any result.

select *
  from ( select address,name,  TIMESTAMP          
                 , ROW_NUMBER() OVER ( PARTITION BY address.name ORDER BY  TIMESTAMP DESC ) rn
            FROM tablename
 ) sq
where sq.rn <= 3 and to_char(timestamp,'DD-MM-YYYY')='01-12-2012'

Open in new window

thanks for help

Guy Hengel [angelIII / a3]

this query will only return results if users have such timestamp values as top 3.
not sure if that will be really the case?!

hi4ppl

ASKER

Well partially yes but I would like to add to this that what I want is that go grab the latest three record of that username he might have done 1000 transaction but I want to grab the latest three from that day and im not sure if it does that... because there is no way that I don't have latest 3 record out million record which this query returns zero .

thanks

Guy Hengel [angelIII / a3]

then you have to move that condition to the subquery

select *
  from ( select address,name,  TIMESTAMP          
                 , ROW_NUMBER() OVER ( PARTITION BY address.name ORDER BY  TIMESTAMP DESC ) rn
            FROM tablename
       WHERE to_char(timestamp,'DD-MM-YYYY')='01-12-2012'
 ) sq
where sq.rn <= 3

Open in new window

hi4ppl

ASKER

Guy Hengel [angelIII / a3]

you can do that "finetuning" like this.

select address, timestamp, name
  from ( select address,name,  TIMESTAMP          
                 , ROW_NUMBER() OVER ( PARTITION BY address.name ORDER BY  TIMESTAMP DESC ) rn
            FROM tablename
       WHERE to_char(timestamp,'DD-MM-YYYY')='01-12-2012'
 ) sq
where sq.rn <= 3   
order by sq.name, rn

Open in new window

hi4ppl

ASKER

Hi thanks I get this error it says the column sq.name does not exist what exactly that column is? thanks

Guy Hengel [angelIII / a3]

I presume you don't use the sql as I wrote it ...

the "sq." refers to the subquery alias ...

hi4ppl

ASKER

Hi no I run it as it's no changes the only things I do is changing the fields and database name to it's actual

Guy Hengel [angelIII / a3]

let's see... you put FROM tablename, but PARTITION BY address.name ...
so, there MUST be something you change in the query ... or you need to change ...

select *
  from ( select address, name,  TIMESTAMP          
                 , ROW_NUMBER() OVER ( PARTITION BY address.name ORDER BY  TIMESTAMP DESC ) rn
            FROM address
    WHERE to_char(timestamp,'DD-MM-YYYY')='01-12-2012'
 ) sq
where rn <= 3
order by name, rn

Open in new window

or, you changed the "." between address.name there into a comma (which is wrong in regards to the required results)

select *
  from ( select address, name,  TIMESTAMP          
                 , ROW_NUMBER() OVER ( PARTITION BY name ORDER BY  TIMESTAMP DESC ) rn
            FROM yourtablename
    WHERE to_char(timestamp,'DD-MM-YYYY')='01-12-2012'
 ) sq
where rn <= 3
order by name, rn

Open in new window

hi4ppl

ASKER

still it does not give me the result that I was asking I m running the same query but changing the files, I cannot post the exact fields if there is way I can send you PM? I'm not sure if there is any?

Guy Hengel [angelIII / a3]

Check my profile

hi4ppl

ASKER

Hi I have manage to make the changes on this but when I run I get the following I don't even know what RN means here I thought it shows number of records that this account have but when I check on the same date it has only one which shows the output.

amount	accountnumber	timestamp	rn
200	          4872036	1/1/2013 10:38	1
200	          4903993	1/1/2013 10:31	2
200	          2442270	1/1/2013 10:30	3
3000	7728031	        1/1/2013 23:59	1
3000	2161910	        1/1/2013 23:59	2
5000	7145383	        1/1/2013 23:58	3
9900	2145034	       1/1/2013 12:04	1

Open in new window

with the bellow changes on the query

select *
  from ( select amount, accountnumber,  TIMESTAMP          
                 , ROW_NUMBER() OVER ( PARTITION BY amount ORDER BY  TIMESTAMP DESC ) rn
            FROM accounts
    WHERE to_char(timestamp,'DD-MM-YYYY')='01-01-2013'
 ) sq
where rn <= 3
and amount>0 
order by amount, rn

Open in new window

I'm not sure if I didn't make myself clear as there is misunderstand here but the result that i'm looking for from this would be bellow

amount	accountnumber	timestamp	
200	          4872036	1/1/2013 10:38	
500	          4872036	1/2/2013 10:38	
600	          4872036	1/3/2013 10:38	
200	          4903993	1/1/2013 10:31	
700	          4903993	1/3/2013 10:31	
600	          4903993	1/4/2013 10:31

Open in new window

this should continue grabbing 3 records for each account the latest one or the old one no matter how many records they have I need the three latest one

thanks for the help.

Guy Hengel [angelIII / a3]

so you want to PARTITION BY account, and not by amount ...
also, you want to move the AMOUNT> 0 condition to the subquery, I think

RN is the alias for the ROW_NUMBER() expression.

select *
  from ( select amount, accountnumber,  TIMESTAMP          
                 , ROW_NUMBER() OVER ( PARTITION BY accountnumber ORDER BY  TIMESTAMP DESC ) rn
            FROM accounts
    WHERE to_char(timestamp,'DD-MM-YYYY')='01-01-2013'
    and amount>0 
 ) sq
where rn <= 3
order by accountnumber , rn

Open in new window

hi4ppl

ASKER

Okay thanks I got the result so the result that is coming in the rn what does that means like I have numbers their from 1-3 ?

thanks for help

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hi4ppl

ASKER

thanks