CODEWITHSUNDEEP
sql-server-2008
F.Mysir
F.Mysir

Reputation: 4106

Trying to get the top two records from table SQL Server

I am trying to get the first record the first time and then the second record with the second query. I am using those two queries but they return the same thing:

SELECT M.CODE,
ISNULL((SELECT TOP 1 *
FROM (
SELECT TOP 1 PRICE AS PRICE FROM MTRLINES ML WHERE ML.MTRL=M.MTRL AND FINDOC IN (SELECT FINDOC FROM FINDOC WHERE SOSOURCE=1251 AND FPRMS IN (1,2)) ORDER BY FINDOC DESC ) T
ORDER BY PRICE),0) AS B
FROM MTRL M
WHERE M.SODTYPE=51 AND M.COMPANY=1 AND M.CODE=:kod_an
ORDER BY M.CODE

This gets the first record then trying to execute this query to get the second record:

SELECT M.CODE,
ISNULL((SELECT TOP 1 *
FROM (
SELECT TOP 2 PRICE AS PRICE FROM MTRLINES ML WHERE ML.MTRL=M.MTRL AND FINDOC IN (SELECT FINDOC FROM FINDOC WHERE SOSOURCE=1251 AND FPRMS IN (1,2)) ORDER BY FINDOC DESC ) T
ORDER BY PRICE),0) AS B
FROM MTRL M
WHERE M.SODTYPE=51 AND M.COMPANY=1 AND M.CODE=:kod_an
ORDER BY M.CODE

But it returns the same result. I am still learning thanks!

Edit:

Just a simple illustration just to get the idea: User passes the variable :kod_an

I have two tables for example:

|MTRL|  CODE  |
---------------
| 1  | 080109 |
| 2  | 085145 |
| 3  | 084141 |


|MTRL|  PRICE  | FINDOC |
-------------------------
| 1  |  4.95   | 12345  |
| 1  |  4.50   | 23421  |
| 1  |  3.90   | 23499  |

So the user is searching for the code (:kod_an) the last two prices (in our example :kod_an=080109). My output should be 3.90 in the first query and 4.50 in the second query. Findoc should have descending order, in order to select the first time the last one and the second time the second one from the top this time!

To conclude my queries is showing 3.90 in both situations. I do not know why...

Upvotes: 0

Views: 62

Answers (2)

F.Mysir
F.Mysir

Reputation: 4106

With the help of SqlZim I have achieved what I was looking for:

SELECT 
        SUB.CODE
      , SUB.PRICE
FROM (
        SELECT 
              M.CODE
              , ML.PRICE
              , rn = row_number() over (PARTITION BY M.CODE ORDER BY FINDOC DESC)
        FROM  MTRLINES ML
              INNER JOIN MTRL M
                    ON M.MTRL = ML.MTRL  AND FINDOC IN (SELECT FINDOC FROM FINDOC WHERE SOSOURCE=1251 AND FPRMS IN (1,2)) 
              WHERE  M.SODTYPE=51 AND M.COMPANY=1 AND M.CODE=:kod_an
      ) sub
WHERE rn IN (1,2)

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

using row_number().

select 
    sub.code
  , sub.price
from (
    select 
        m.code
      , ml.price
      , rn = row_number() over (partition by m.code, order by price)
    from mtrl m
      inner join mtrlines ml
        on m.mtrl = ml.mtrl
    where m.code=:kod_an
  ) sub
where rn in (1,2)

rextester demo: http://rextester.com/VQIGT77173

returns: 

+---+-------+-------+
|   | code  | price |
+---+-------+-------+
| 1 | 80109 |     4 |
| 2 | 80109 |     5 |
+---+-------+-------+

You could repeat the above once for rn = 1 and again for rn = 2 if you want two results sets for some reason.

Upvotes: 1

Related Questions