simple sql report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

simple sql report

Author

Topic

rango316
Starting Member

20 Posts

Posted - 2008-01-03 : 06:11:00

Hi,

I have a simple report written in SQL and I only want to show the TOP (2) records by date for each branch...

When i try and use the TOP (2) command it gives me only the very first 2 records...

my code is as follows:

SELECT REPLACE(CONVERT(VARCHAR, FilteredNew_Visit.new_startutc, 3), '/', '/') AS new_startutc, REPLACE(CONVERT(VARCHAR,
FilteredNew_Visit.new_endutc, 3), '/', '/') AS new_endutc, FilteredAccount.accountid, FilteredAccount.new_branch, FilteredAccount.name,
FilteredAccount.new_versionmbaname, FilteredAccount.new_versionwename, FilteredAccount.new_primarytsmname, FilteredNew_Visit.new_typename,
FilteredNew_Visit.new_name, FilteredNew_Visit.new_endutc AS Expr2, FilteredNew_Visit.new_startutc AS Expr1,
FilteredNew_Visit.new_accountid
FROM FilteredNew_Visit INNER JOIN
FilteredAccount ON FilteredAccount.accountid = FilteredNew_Visit.new_accountid
ORDER BY FilteredAccount.name, Expr1 DESC

can anyone help...i dont think i have explained this very well..

sorry total newbie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 06:19:00

If its SQL 2005 then use this:-

SELECT * FROM
(

SELECT 
ROW_NUMBER() OVER (PARTITION BY FilteredAccount.new_branch ORDER BY (datefield) (DESC) AS 'RowNo',
REPLACE(CONVERT(VARCHAR, FilteredNew_Visit.new_startutc, 3), '/', '/') AS new_startutc, REPLACE(CONVERT(VARCHAR, 
FilteredNew_Visit.new_endutc, 3), '/', '/') AS new_endutc, FilteredAccount.accountid, FilteredAccount.new_branch, FilteredAccount.name, 
FilteredAccount.new_versionmbaname, FilteredAccount.new_versionwename, FilteredAccount.new_primarytsmname, FilteredNew_Visit.new_typename, 
FilteredNew_Visit.new_name, FilteredNew_Visit.new_endutc AS Expr2, FilteredNew_Visit.new_startutc AS Expr1, 
FilteredNew_Visit.new_accountid
FROM FilteredNew_Visit INNER JOIN
FilteredAccount ON FilteredAccount.accountid = FilteredNew_Visit.new_accountid)t
WHERE t.RowNo<=2
ORDER BY t.name, t.Expr1 DESC

where datefield is field containing your date value.Use DESC only if you want top 2 records based on decreasing order of date.

rango316
Starting Member

20 Posts

Posted - 2008-01-03 : 06:24:18

thank you.. wow totally out of my league but will try on wit this im sure you can help

i get this error:

Error in ORDER BY clause.
Error in list of function arguments: 'AS' not recognized.
Unable to parse query text.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 06:30:09

missed a closing bracket

SELECT * FROM
(

SELECT
ROW_NUMBER() OVER (PARTITION BY FilteredAccount.new_branch ORDER BY (datefield) (DESC)) AS 'RowNo',
REPLACE(CONVERT(VARCHAR, FilteredNew_Visit.new_startutc, 3), '/', '/') AS new_startutc, REPLACE(CONVERT(VARCHAR,
FilteredNew_Visit.new_endutc, 3), '/', '/') AS new_endutc, FilteredAccount.accountid, FilteredAccount.new_branch, FilteredAccount.name,
FilteredAccount.new_versionmbaname, FilteredAccount.new_versionwename, FilteredAccount.new_primarytsmname, FilteredNew_Visit.new_typename,
FilteredNew_Visit.new_name, FilteredNew_Visit.new_endutc AS Expr2, FilteredNew_Visit.new_startutc AS Expr1,
FilteredNew_Visit.new_accountid
FROM FilteredNew_Visit INNER JOIN
FilteredAccount ON FilteredAccount.accountid = FilteredNew_Visit.new_accountid)t
WHERE t.RowNo<=2
ORDER BY t.name, t.Expr1 DESC

rango316
Starting Member

20 Posts

Posted - 2008-01-03 : 06:33:14

Error in ORDER BY clause.
Error in list of function arguments: 'AS' not recognized.
Unable to parse query text.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 06:36:50

Are you using () before datefield and DESC? they are not required. I just put them to show that they need to substituted by your fieldname or are optional.

rango316
Starting Member

20 Posts

Posted - 2008-01-03 : 06:41:55

I get this:

Error in ORDER BY clause.
Unable to parse query text.

this is my code

SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY FilteredAccount.new_branch
ORDER BY () AS 'RowNo', REPLACE(CONVERT(VARCHAR, FilteredNew_Visit.new_startutc, 3), '/', '/') AS new_startutc, REPLACE(CONVERT(VARCHAR,
FilteredNew_Visit.new_endutc, 3), '/', '/') AS new_endutc, FilteredAccount.accountid, FilteredAccount.new_branch, FilteredAccount.name,
FilteredAccount.new_versionmbaname, FilteredAccount.new_versionwename, FilteredAccount.new_primarytsmname,
FilteredNew_Visit.new_typename, FilteredNew_Visit.new_name, FilteredNew_Visit.new_endutc AS Expr2,
FilteredNew_Visit.new_startutc AS Expr1, FilteredNew_Visit.new_accountid
FROM FilteredNew_Visit INNER JOIN
FilteredAccount ON FilteredAccount.accountid = FilteredNew_Visit.new_accountid) t
WHERE t .RowNo <= 2
ORDER BY t .name, t .Expr1 DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 06:45:19

Put your datefield in the ----- below & execute

SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY FilteredAccount.new_branch
ORDER BY ------) AS 'RowNo', REPLACE(CONVERT(VARCHAR, FilteredNew_Visit.new_startutc, 3), '/', '/') AS new_startutc, REPLACE(CONVERT(VARCHAR,
FilteredNew_Visit.new_endutc, 3), '/', '/') AS new_endutc, FilteredAccount.accountid, FilteredAccount.new_branch, FilteredAccount.name,
FilteredAccount.new_versionmbaname, FilteredAccount.new_versionwename, FilteredAccount.new_primarytsmname,
FilteredNew_Visit.new_typename, FilteredNew_Visit.new_name, FilteredNew_Visit.new_endutc AS Expr2,
FilteredNew_Visit.new_startutc AS Expr1, FilteredNew_Visit.new_accountid
FROM FilteredNew_Visit INNER JOIN
FilteredAccount ON FilteredAccount.accountid = FilteredNew_Visit.new_accountid) t
WHERE t .RowNo <= 2
ORDER BY t .name, t .Expr1 DESC

rango316
Starting Member

20 Posts

Posted - 2008-01-03 : 06:48:51

Error in FROM clause: near ')'.
Unable to parse query text.

code =

SELECT *
FROM (SELECY ROW_NUMBER() OVER (PARTITION BY FilteredAccount.new_branch
ORDER BY new_startutc) AS 'RowNo', REPLACE(CONVERT(VARCHAR, FilteredNew_Visit.new_startutc, 3), '/', '/') AS new_startutc, REPLACE(CONVERT(VARCHAR,
FilteredNew_Visit.new_endutc, 3), '/', '/') AS new_endutc, FilteredAccount.accountid, FilteredAccount.new_branch, FilteredAccount.name,
FilteredAccount.new_versionmbaname, FilteredAccount.new_versionwename, FilteredAccount.new_primarytsmname, FilteredNew_Visit.new_typename,
FilteredNew_Visit.new_name, FilteredNew_Visit.new_endutc AS Expr2, FilteredNew_Visit.new_startutc AS Expr1, FilteredNew_Visit.new_accountid
FROM FilteredNew_Visit INNER JOIN
FilteredAccount ON FilteredAccount.accountid = FilteredNew_Visit.new_accountid) t
WHERE t .RowNo <= 2
ORDER BY t .name, t .Expr1 DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 06:51:27

then i guess there must ne some error with your subquery. Can you try running subquery alone?

i.e
SELECY ROW_NUMBER() OVER (PARTITION BY FilteredAccount.new_branch
ORDER BY new_startutc) AS 'RowNo', REPLACE(CONVERT(VARCHAR, FilteredNew_Visit.new_startutc, 3), '/', '/') AS new_startutc, REPLACE(CONVERT(VARCHAR,
FilteredNew_Visit.new_endutc, 3), '/', '/') AS new_endutc, FilteredAccount.accountid, FilteredAccount.new_branch, FilteredAccount.name,
FilteredAccount.new_versionmbaname, FilteredAccount.new_versionwename, FilteredAccount.new_primarytsmname, FilteredNew_Visit.new_typename,
FilteredNew_Visit.new_name, FilteredNew_Visit.new_endutc AS Expr2, FilteredNew_Visit.new_startutc AS Expr1, FilteredNew_Visit.new_accountid
FROM FilteredNew_Visit INNER JOIN
FilteredAccount ON FilteredAccount.accountid = FilteredNew_Visit.new_accountid

rango316
Starting Member

20 Posts

Posted - 2008-01-03 : 06:55:11

that works fine

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 06:58:35

Is this part of some other query then?

rango316
Starting Member

20 Posts

Posted - 2008-01-03 : 07:00:41

no none whatsoever

rango316
Starting Member

20 Posts

Posted - 2008-01-03 : 07:25:57

IT WORKS...sorry Visakh!

it throws up an error but it can get past it and it works like a dream thank you so much!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-03 : 07:51:50

quote:
Originally posted by visakh16

then i guess there must ne some error with your subquery. Can you try running subquery alone?

i.e
SELECY ROW_NUMBER() OVER (PARTITION BY FilteredAccount.new_branch
ORDER BY new_startutc) AS 'RowNo', REPLACE(CONVERT(VARCHAR, FilteredNew_Visit.new_startutc, 3), '/', '/') AS new_startutc, REPLACE(CONVERT(VARCHAR,
FilteredNew_Visit.new_endutc, 3), '/', '/') AS new_endutc, FilteredAccount.accountid, FilteredAccount.new_branch, FilteredAccount.name,
FilteredAccount.new_versionmbaname, FilteredAccount.new_versionwename, FilteredAccount.new_primarytsmname, FilteredNew_Visit.new_typename,
FilteredNew_Visit.new_name, FilteredNew_Visit.new_endutc AS Expr2, FilteredNew_Visit.new_startutc AS Expr1, FilteredNew_Visit.new_accountid
FROM FilteredNew_Visit INNER JOIN
FilteredAccount ON FilteredAccount.accountid = FilteredNew_Visit.new_accountid

When you convert values to varchar datatype, always specify the length
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources