I'm trying to do something in a single query and can't quite get my head
around it, so would appreciate any hints!

I'm using SQL Server 2000.

Say I have a table like this:

CREATE TABLE test (
something int not null,
major_seq int not null,
minor_seq int not null,
constraint unique(something,major_seq,minor_seq)
);

I want to make a SELECT that will return, for a given value of something,
a single row with the highest value of major_seq, and the highest value of
minor seq+1 for that major_seq. If there are no records for the given value
of something, I want it still to return a single row, containing 1,1.

Without my final sentence, I could do:

SELECT TOP 1 major_seq,minor_seq+1
FROM test
WHERE something=N
ORDER BY major_seq DESC,minor_seq DESC

But I want to be able to use the SELECT in an INSERT, so need it still
to return a row if the WHERE clause doesn't match. I tried UNION with
SELECT 1,1 after it, but it complained at the ORDER clause.

I also tried:

SELECT ISNULL(major_seq,1),ISNULL(MAX(minor_seq),0)+1
FROM test t1
WHERE something=N AND major_seq=(
SELECT MAX(major_seq) FROM test t2 WHERE t2.something=t1.something
)
GROUP BY ISNULL(major_seq,1)

which worked if something=N matched, but returned zero rows if it didn't.
Why did an aggregate query return no rows anyway?

It feels like I ought to be able to do it with a self-join and ISNULL,
but I can't work it out.

Any ideas would be gratefully received!

Cheers
Tony
--
Tony Mountifield
Work: tony@softins.co.uk - http://www.softins.co.uk
Play: tony@mountifield.org - http://tony.mountifield.org

Here is one way:

SELECT COALESCE(MAX(major_seq), 1),
COALESCE(MAX(minor_seq1), 1)
FROM (
SELECT TOP 1 major_seq, minor_seq + 1 AS minor_seq1
FROM test
WHERE something = N
ORDER BY major_seq DESC, minor_seq DESC) AS T;


--
Plamen Ratchev
http://www.SQLStudio.com

In article <eoudnaRmloomMVvVnZ2dnUVZ_v_inZ2d@speakeasy.net> ,
Plamen Ratchev <Plamen@SQLStudio.com> wrote:
> Here is one way:
>
> SELECT COALESCE(MAX(major_seq), 1),
> COALESCE(MAX(minor_seq1), 1)
> FROM (
> SELECT TOP 1 major_seq, minor_seq + 1 AS minor_seq1
> FROM test
> WHERE something = N
> ORDER BY major_seq DESC, minor_seq DESC) AS T;

Many thanks! I was close, but hadn't thought to wrap another aggregate
select around my original query.

I did find my own solution in the meantime:

SELECT TOP 1 major_seq,minor_seq+1 FROM (
SELECT major_seq,minor_seq FROM test WHERE something=N
UNION
SELECT 1,0
) AS x ORDER BY major_seq DESC,minor_seq DESC;

But I suspect yours is more efficient as it only needs to return one row
from the derived table.

Cheers
Tony
--
Tony Mountifield
Work: tony@softins.co.uk - http://www.softins.co.uk
Play: tony@mountifield.org - http://tony.mountifield.org

I had to throw this in, even tho you will need some ANSI Standard
features that you don't have in you version of SQL Server. Save it
for the next upgrade

SELECT X.*
FROM (SELECT @my_thing, major_seq, minor_seq
ROW_NUMBER ()
OVER (ORDER BY major_seq DESC, minor_seq DESC) AS
sort_seq
FROM Foobar
UNION
VALUES (@my_thing, 1, 1) -- row constructor
WHERE something = @my_thing)
AS X(something, major_seq, minor_seq, sort_seq)
WHERE X.sort_seq = 1; -- this can be changed