SQL Server Query (complicated)

[Murali]

Folks,

I have the following data in a table:

4 NULL NULL
2 abc NULL
2 aaa NULL
4 xyz NULL
4 xyz pqr
4 pyz xqr

I want to get only one record for each record number. that is, the
result set should be like this:

4 NULL NULL
2 abc NULL

Please suggest how the query should be built. Thanks for the help.

Murali

[Simon Hayes]

Unfortunately, it's not easy to give a good answer without more information.
From what you have posted below, it's not clear why you want those two those
records and not one of the others, unless perhaps there is another column
which you didn't include and that you use to order by.

I suggest you post a CREATE TABLE for your table, with some INSERT
statements to add test data, and then a sample result set that you would
like to see.

Simon

"Murali" <mu*************@hotmail.com> wrote in message
news:62************************@posting.google.com ...

Folks,

I have the following data in a table:

4 NULL NULL
2 abc NULL
2 aaa NULL
4 xyz NULL
4 xyz pqr
4 pyz xqr

I want to get only one record for each record number. that is, the
result set should be like this:

4 NULL NULL
2 abc NULL

Please suggest how the query should be built. Thanks for the help.

Murali

[Murali]

Thanks Anith & others.

I think I confused you. I'm sorry. What I want exactly is, ONLY ONE
RECORD for each <col1>. I don't care whether it's first or last. But
it's better if I get the first record.

Ex: Say I've this data in test table:
Pno Pname
1 xxxx
1 NULL
1 YYYYY
2 NULL
2 abcd

I want the result to be:

1 xxxxx
2 NULL

Hope, I'm clear this time. Thanks for the help.

Murali

"Anith Sen" <an***@bizdatasolutions.com> wrote in message news:<6P*********************@bgtnsc04-news.ops.worldnet.att.net>...

First, add keys to your tables. Then, if possible, try not to use NULLs as
logical values. In any case, do:

SELECT *
FROM tbl
WHERE COALESCE(col2, 'aaa') =
(SELECT TOP 1 COALESCE(t1.col2, 'aaa')
FROM tbl t1
WHERE t1.col1 = tbl.col1
ORDER BY t1.col2);

[Anith Sen]

>> But it's better if I get the first record. <<

There is no 'first' & 'last' rows in SQL. In fact logically a table is a set
of rows & by definition of sets the rows have no order. Data is retrieved
based on the values in the table, not by position of rows.

For the example in your second post, you can do:

SELECT Pno, MAX(Pname) --- or MIN
FROM tbl
GROUP BY Pno ;

If you have other columns, here is a generalized soln :

SELECT *
FROM tbl
WHERE keycol = ( SELECT TOP 1 t1.keycol
FROM tbl t1
WHERE t1.dupcol = tbl.dupcol
ORDER BY t1.uniqueCol DESC ) ;

In the above, uniquecol is the column or set of columns which can uniquely
identify a row per group (pname in your case) in the table & dupcol is the
column which is being duplicated (pno in your case )

--
- Anith
( Please reply to newsgroups only )