Select statement help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Select statement help

Author

Topic

bjb
Starting Member

6 Posts

Posted - 2010-09-14 : 14:18:10

Hello,
I really need help figuring out how to setup my SELECT statement.
I have two tables DETAILS and CONTAINER Each table has a primary key of CUSID AND REFERENCE. Container table also has the container number as part of key. There can be many container records for a single reference. I want to list all references from details but only the first record from containers. The user can click the reference if they want to see more details such as all of the containers that go with that reference. Here is my query so far and how it looks and how I want it to look

SELECT REFNO, [cont-no]
FROM DETAILS LEFT JOIN container ON
DETAILS.REFNO = container.[cont-ref] AND DETAILS.USERID = container.[cont-cusid]
WHERE DETAILS.USERID = '{USERID}'

Output example
Ref Container No
123456 0000001
123456 0000002
123456 0000003
222222 0000001
222222 0000002
333333 0000033

But I want it to look like this
Ref Container No.
123456 0000001
222222 0000001
333333 0000033

Any help would be appreciated.
Thanks
Ben

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-09-14 : 14:38:00

Try this:


SELECT REFNO, [cont-no]
  FROM (SELECT D.REFNO, C.[cont-no], ROW_NUMBER() OVER(PARTITION BY D.REFNO ORDER BY C.[cont-no]) AS row_num
          FROM DETAILS AS D
               LEFT JOIN
               container AS C
               ON D.REFNO = C.[cont-ref]
                  AND D.USERID = C.[cont-cusid]
         WHERE D.USERID = '{USERID}') AS T
 WHERE row_num = 1

bjb
Starting Member

6 Posts

Posted - 2010-09-14 : 15:07:37

Thanks for the quick reply. However that gives the error

Form: Grid DETAILS
Error: 'ROW_NUMBER' is not a recognized function name. (Microsoft SQL Server Native Client 10.0)

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-09-14 : 15:19:23

If your are using SQL Server 2008 then it should works. So what is the version of SQL Server you are using? and what is the compatibility level of the database?

bjb
Starting Member

6 Posts

Posted - 2010-09-14 : 15:25:22

I believe it is SQL 2005(hosted online) and the compatibility level is 8 According to the properties when I looked using the program SQL Manager2008

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-09-14 : 15:37:00

Your database level is in SQL Server 2000 compatibility level (it is 80). So you can't use ROW_NUMBER unless you change the compatibility level to a higher level (as it is a 2005+ feature).
If you can change the compatibility level then it is good, or you should write the query to use only SQL Server 2000 features.

bjb
Starting Member

6 Posts

Posted - 2010-09-14 : 16:10:43

I checked with my hosting company and they said I'm running MSSQL Server 2005 but I don't know what the compatibility number is.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-09-14 : 16:54:31

Run this query to find the compatibility level of your database (the database your using in your connection):


SELECT D.compatibility_level
  FROM sys.databases AS D
 WHERE D.database_id = DB_ID()

If it is 80 then your database compatibility level is SQL Server 2000.

bjb
Starting Member

6 Posts

Posted - 2010-09-17 : 16:30:31

I finally got my hosting company to switch me to 2005 server even though I was already paying for it....

Your code worked and I just need to add some fields and two more tables but I should be able to copy what you did.

Thank you very much.

bjb
Starting Member

6 Posts

Posted - 2010-09-20 : 13:50:34

Malpashaa, Could you help me again. I need to add some search fields to this query but don't know where to fit them in. Here is your example with my additional fields. Below are the fields that I want to have searchable in the results.

SELECT REFNO, PKGS, PKGSDESC, COMMODITY, ARRIVALDATE, LASTUPDATED, [cont-no], [po-po],[man-mstr-bl], [man-house-bl], [man-subhouse-bl]
FROM (SELECT D.REFNO, D.PKGS, D.PKGSDESC, D.COMMODITY, D.ARRIVALDATE, D.LASTUPDATED, P.[po-po], C.[cont-no], M.[man-mstr-bl], M.[man-house-bl], M.[man-subhouse-bl],
ROW_NUMBER() OVER(PARTITION BY D.REFNO ORDER BY C.[cont-no]) AS row_num
FROM DETAILS AS D
LEFT JOIN container AS C
ON D.REFNO = C.[cont-ref]
AND D.USERID = C.[cont-cusid]
LEFT JOIN pofile AS P
ON D.REFNO = P.[po-ref]
AND D.USERID = P.[po-cusid]
LEFT JOIN manifest AS M
ON D.REFNO = M.[man-ref]
AND D.USERID = M.[man-cusid]
WHERE D.USERID = '{USERID}') AS T
WHERE row_num = 1

Here is the code I used to have to search with

AND DETAILS.REFNO = '{s_REFNO}'
OR DETAILS.ARRIVALDATE = '{s_ARRIVALDATE}'
OR DETAILS.CUSTREFNO LIKE '%{s_CUSTREFNO}%'
OR DETAILS.ENTRYNO LIKE '%{s_ENTRYNO}%'
OR DETAILS.BILLLADING LIKE '%{s_BILLLADING}%'
OR DETAILS.HAWBNO LIKE '%{s_HAWBNO}%'
OR DETAILS.AWBNO LIKE '%{s_AWBNO}%'

Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources