Select statement problem - 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 2000 Forums

Transact-SQL (2000)

Select statement problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-20 : 09:22:18

Tom Ryan writes "SQL Server 7.0
Windows NT 4.0

To be brief:

Table:
Jobcode
bofenumber
Invoicenumber
Revisionnumber

Entries:
"G", "G001", 1, ''
"G", "G002", 2, ''
"G", "G002A", 2, 'A'
"G", "G002B", 2, 'B'
"G", "G003", 3, ''
"G", "G003A", 3, 'A'

Problem:
I want highest number bofenumber, in this example, I want the return set to be:

"G001"
"G002B"
"G003A"

What I try:
SELECT a.invoicenumber, revisionnumber
FROM bofelisting a
WHERE a.jobcode = 'g' AND a.revisionnumber =
(SELECT (MAX(revisionnumber))
FROM bofelisting
WHERE a.invoicenumber = invoicenumber)

What it returns:
"G002B"

Questions:
1. Why doesn't the max function return "G001" or "G003A"
2. What is the intelligent way to return this set?
3. Is there enough info here to render an opinion on table
design. Is the fact that invoice number and revision number
are used to make up bofenumber?

Thanks much for your time and expertise

Tom Ryan
EPC"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-08-20 : 10:24:20

SELECT a.invoicenumber, revisionnumber
FROM bofelisting a
WHERE a.jobcode = 'g' AND a.revisionnumber =
        (SELECT (MAX(revisionnumber))
      FROM bofelisting
      WHERE a.invoicenumber = invoicenumber)

this is close, but you'll need a GROUP BY to give you the max(revisionnumber) for each invoice.

if you could (and this is a general request, you should always do this when posting a SELECT question), post the DDL for the table as well as the insert DML for the sample data. that way respondents can simply cut and paste the SQL you provide and quickly create the SELECT statement you're looking for. brevity is nice in many forums but this isn't one of them - the more information the better.

Jonathan Boott, MCDBA
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources