query to get first 2 records
-
Hi,
I need the top and prior to top records from a table for each country. below is my test data.
can you pleaset tell me how we can get the desired result. In oracle we can get this using lead function, but in sql server how to get it
table a
col1 col2 col3
us 01/01/2012 10
us 02/02/2012 11
us 03/03/2012 33
gpb 01/01/2012 20
gbp 02/02/2012 21
chf 01/01/2012 30
output should be
us o3/03/2012 33 02/02/2012 11
gbp 02/01/2012 21 01/01/2012 20
chf 01/01/2012 30
Thanks & regards
Naveen
-
This should do the trick:
DECLARE @a TABLE (
col1 varchar(3),
col2 date,
col3 int
);
INSERT INTO @a
SELECT 'us', '01/01/2012', 10
UNION ALL SELECT 'us', '02/02/2012', 11
UNION ALL SELECT 'us', '03/03/2012', 33
UNION ALL SELECT 'gbp', '01/01/2012', 20
UNION ALL SELECT 'gbp', '02/02/2012', 21
UNION ALL SELECT 'chf', '01/01/2012', 30;
WITH data AS (
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2 DESC)
FROM @a
), filteredData AS (
SELECT *
FROM data
WHERE RN <= 2
)
SELECT f.col1, f.col2, f.col3, p.col2, p.col3
FROM filteredData AS f
OUTER APPLY (
SELECT *
FROM filteredData
WHERE col1 = f.col1
AND RN = 2
) AS p
WHERE f.RN = 1
ORDER BY col1
-- Gianluca Sartori
-
DECLARE @t TABLE(col1 VARCHAR(3), col2 DATETIME, col3 INT)
INSERT INTO @t(col1,col2,col3)
SELECT 'us', '01/01/2012', 10 UNION ALL
SELECT 'us', '02/02/2012', 11 UNION ALL
SELECT 'us', '03/03/2012', 33 UNION ALL
SELECT 'gbp', '01/01/2012', 20 UNION ALL
SELECT 'gbp', '02/02/2012', 21 UNION ALL
SELECT 'chf', '01/01/2012', 30;
WITH CTE AS (
SELECT col1,col2,col3,
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col3 DESC) AS rn
FROM @t)
SELECT col1,
MAX(CASE WHEN rn=1 THEN col2 END) AS col2First,
MAX(CASE WHEN rn=1 THEN col3 END) AS col3First,
MAX(CASE WHEN rn=2 THEN col2 END) AS col2Second,
MAX(CASE WHEN rn=2 THEN col3 END) AS col3Second
FROM CTE
GROUP BY col1;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
thanks, but can you tell me the same how we can write in MSAccess as it doesnt support partition and with.
Looking in MS-Access
Thanks & Regards
Naveen
-
You're better off asking here
http://www.sqlservercentral.com/Forums/Forum131-1.aspx
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
even if you can suggest me the general query which will work in all databases it would be great
-
ekknaveen (2/13/2012)
even if you can suggest me the general query which will work in all databases it would be greatI don't think such a syntax exists. I'm afraid you will have to code this for each RDBMS you're working on.
@mark-3: nice one!
-- Gianluca Sartori
-
Gianluca Sartori (2/13/2012)
ekknaveen (2/13/2012)
even if you can suggest me the general query which will work in all databases it would be greatI don't think such a syntax exists. I'm afraid you will have to code this for each RDBMS you're working on.
@mark-3: nice one!
Thanks!
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RAISED THIS IN ACCESS FORUM, BUT IF ANY OF THEM CAN GIVE ME A IDEA OF HOW TO GET IT IN ACCESS, WILL BE GREAT
-
ekknaveen (2/13/2012)
RAISED THIS IN ACCESS FORUM, BUT IF ANY OF THEM CAN GIVE ME A IDEA OF HOW TO GET IT IN ACCESS, WILL BE GREATTry this link. http://bit.ly/zjNFeT
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
try this
SELECT Q.col1, Last(Q.col2) AS Top1DATE, Last(Q.col3) AS Top1QTY, First(Q.col2) AS Top2DATE, First(Q.col3) AS Top2QTY
FROM (SELECT col1, col2, col3
FROM tableA
WHERE col3 IN
(select top 2 col3 from tableA as S
where s.col1 = tableA.col1
order by col3 desc
)
ORDER BY col1, col3 DESC) AS Q
GROUP BY Q.col1;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Hello,
once I had a similar request and I decided to create a ROW_NUMBER function in Access, like
Private last_Row_Number As LongPrivate key_Row_Number As String
Public Function Initialize_Row_Number() As Long
last_Row_Number = 0
key_Row_Number= ""
End Function
Public Function Row_Number(groupingKey As String) As Long
If groupingKey = key_Row_Number Then
last_Row_Number = last_Row_Number + 1
Else
last_Row_Number = 1
key_Row_Number = groupingKey
End If
Row_Number = last_Row_Number
End Function
Then you need to define your queries in Access like that,
-- Query1SELECT country, date, value, Row_Number(country) AS RowNumber
FROM Table1 ORDER BY country, date DESC
-- Query2
SELECT * FROM Query1 WHERE RowNumber = 1
-- Query3
SELECT * FROM Query1 WHERE RowNumber = 2
-- Query4
SELECT * FROM Query2 LEFT JOIN Query3 ON Query2.country = Query3.country
You should call the function Initialize_Row_Number before running your queries.
Hope this helps,
Francesc
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply