unable to use LIMIT when using correlated query

Question

I have two tables in Postgres. I want to get the latest 3records data from table.

Below is the query:

select two.sid as sid,
       two.sidname as sidname,
       two.myPercent as mypercent,
       two.saccur as saccur,
       one.totalSid as totalSid 
from table1 one,table2 two 
where one.sid = two.sid;

The above query displays all records checking the condition one.sid = two.sid;I want to get only recent 3 records data(4,5,6) from table2.

I know in Postgres we can use limit to limit the rows to retrieve, but here in table2 for each ID I have multiple rows. So I guess I cannot use limit on table2 but should use on table1. Any suggestions?

table1:

sid totalSid
1   10
2  20
3  30
4  40
5  50
6  60

table2:

sid sidname myPercent saccur 
1   aaaa        11    11t
1   bbb         13    13g
1   ccc         11    11g
1   qw          88    88k
//more data for 2,3,4,5....
6    xyz    89    895W     
6    xyz1       90    90k
6    xyz2       91    91p
6    xyz3       92    92q

Lookup the use of `cross apply` with your limit instead of a `cross join` or a `,` join I think posgresql calls this a lateral join. https://stackoverflow.com/questions/11472790/postgres-analogue-to-cross-apply-in-sql-server How do you identify "Recent?" — xQbert, Aug 28 '17 at 19:40
Limit alone would not work because it's applicable to the entire joined set. Thus you couldn't get the top 3 of each SID. However when combined with a lateral join; the limit is applied to each join between one and two for each joined value (SID) — xQbert, Aug 28 '17 at 19:49
that makes no sense to me. recent is usually in refence to most recent data entered. So looking at your sample data SID 6 has sid names of xyz, xyz1, xyz2, xyz3. How do I know which 3 records are the most recent? sort by name desc? that dos not seem logical. percent and saccur also don't make sense. Can you post sample data from each table and expected results using that sample data? — xQbert, Aug 28 '17 at 20:01
sorry to confuse, i want to show last 3 records data of table1 from table2. i.e.,it should show data from table2 of sid 4,5,6(last 3 records of table1 which are recent in my case) . @xQbert — user7833845, Aug 28 '17 at 20:06

xQbert · Accepted Answer · 2017-08-28T20:42:30.867

Given a changed understanding of the question a simple subquery and join should suffice.

We select everything from table1 limit to 3 records in sid order desc. This gives us the 3 most recent Sid's and then join to table2 to get the other SID relevant data. The assumption here is that SID is unique in table one and "most recent" would be those records having the highest SID.

SELECT two.sid as sid
     , two.sidname as sidname
     , two.myPercent as mypercent
     , two.saccur as saccur
     , one.totalSid as totalSid 
FROM  (SELECT * FROM table1 ORDER BY SID DESC LIMIT 3) one
INNER JOIN table2 two 
  ON one.sid = two.sid;

*note I removed a comma after one alias above.

and below we reinstated the ANSI 88 join syntax using , notation.

SELECT two.sid as sid
     , two.sidname as sidname
     , two.myPercent as mypercent
     , two.saccur as saccur
     , one.totalSid as totalSid 
FROM (SELECT * FROM table1 ORDER BY SID DESC LIMIT 3) one
   , table2 two 
WHERE one.sid = two.sid;

This syntax basically says get the 3 most recent SIDs from table one and cross join (For each record in one match it to all records in two) that to all records in table two but then return only records that have the same SID on both sides. Modern compilers may be able to use Cost based optimization to improve performance here negating the need to do the entire cross join; however, order of operation says this is what the database would normally have to do. if one and two are both tables of substantial size, you can see the cross join could result in a very large temporary dataset

Yes we can. I've revisited the ansi 89 standard in my second SQL statement above. — xQbert, Aug 28 '17 at 20:31
Process/think of data in terms of "SETS" you needed a set of data that was the 3 most recent SIDs and then you needed to improve that set to include related information from table 2. So we 1st get a set with the 3 most recent records, then we joined to table two to get the related information. by using a subquery we forced the engine to "generate" that set then we were able to use it to limit data from table 2! that simple — xQbert, Aug 28 '17 at 20:43
LOL@ `I've revisited the ansi 89 standard` . Next step would be if the engine doesn't implement LIMIT(in subqueries...) — wildplasser, Aug 28 '17 at 20:45
I'd go back to row_number. If that wasn't available then I'd go back to pushing and poping the data off the assembly stack. Hell we're only 28 years removed from the 89 standard... might as well use it in a technology world. I mean pft how far have computers come in the past 28 years anyway... Oo :P In all fairness to user7833845 I get it; I understand we have limits and we adhere to them when we must. — xQbert, Aug 28 '17 at 20:47

unable to use LIMIT when using correlated query

1 Answers1