sql query with date

[Sky]

Hi,

i have this table ABC, that has fields:
iMonth iYear vTitle
1 2011 title1
6 2011 title2
6 2011 title3
12 2011 title4
1 2012 title5
1 2012 title6
2 2012 title7


I need select distinct on the month and year, where it is not the
latest month/year.
In the table above, the latest is Feb 2012
so, I need to populate the entry for the past:

output:
1 2011
6 2011
12 2011
1 2012

what will be the query ?

Many thanks.

Regds,
Sky

[Erland Sommarskog]

Post by Sky
iMonth iYear vTitle
1 2011 title1
6 2011 title2
6 2011 title3
12 2011 title4
1 2012 title5
1 2012 title6
2 2012 title7
I need select distinct on the month and year, where it is not the
latest month/year.
In the table above, the latest is Feb 2012
1 2011
6 2011
12 2011
1 2012
what will be the query ?

WITH numbered AS (
SELECT iMonth, iYear, vTitle,
rank = tank() OVER(ORDER BY iYear DESC, iMonth DESC)
FROM ABC
)
SELECT iMonth, iYear, vTitle,
WHERE rank <> 1

[Sky]

Msg 207, Level 16, State 1, Line 7
Invalid column name 'rank'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'iMonth'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'iYear'.

possible to have query without using rank function ?

Post by Erland Sommarskog

iMonth  iYear   vTitle
1          2011    title1
6          2011    title2
6          2011    title3
12        2011     title4
1          2012    title5
1          2012    title6
2          2012    title7
I need select distinct on the month and year, where it is not the
latest month/year.
In the table above, the latest is Feb 2012
1          2011
6          2011
12        2011
1          2012
what will be the query ?

WITH numbered AS (
   SELECT iMonth, iYear, vTitle,
          rank = tank() OVER(ORDER BY iYear DESC, iMonth DESC)
   FROM  ABC
)
SELECT iMonth, iYear, vTitle,
WHERE  rank <> 1
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[Erland Sommarskog]

Post by Sky
Msg 207, Level 16, State 1, Line 7
Invalid column name 'rank'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'iMonth'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'iYear'.
possible to have query without using rank function ?

There is a FROM clause missing in my response. Where, I leave to you as an
exercise to find out. It should not be too difficult.

If you want a tested solution, you should post:

o CREATE TABLE statements for your table.
o INSERT statement with sample data.
o The desired result given the sample.
o Which version of SQL Server you are using.

This makes it easy to copy and paste into SSMS to develop a tested solution.
It also helps to clarify what you are looking for.

If you are not making this effort, the answer you get is likely to be
untested and may include occasional errors.

[Sky]

Issue resolved. Thanks.

Post by Erland Sommarskog

Post by Sky
Msg 207, Level 16, State 1, Line 7
Invalid column name 'rank'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'iMonth'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'iYear'.
possible to have query without using rank function ?

There is a FROM clause missing in my response. Where, I leave to you as an
exercise to find out. It should not be too difficult.
o  CREATE TABLE statements for your table.
o  INSERT statement with sample data.
o  The desired result given the sample.
o  Which version of SQL Server you are using.
This makes it easy to copy and paste into SSMS to develop a tested solution.
It also helps to clarify what you are looking for.
If you are not making this effort, the answer you get is likely to be
untested and may include occasional errors.
--
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx