Question about Select - 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)

Question about Select

Author

Topic

Mazdak
Yak Posting Veteran

63 Posts

Posted - 2002-12-02 : 15:27:09

I have an table like this:

Date | Priority | PrimaryKey
====== ========= ======
12/2/2002 101 1
12/3/2002 101 2
12/4/2002 101 3
12/5/2002 101 4

12/6/2002 111 5
12/11/2002 111 6
12/12/2002 111 7
12/13/2002 111 8

12/4/2002 121 9
12/14/2002 121 10
12/15/2002 121 12
12/16/2002 121 13

I want to make query command to get ONLY PRIMARYKEY of minimum date for each group of prirority:

12/2/2002 101 1
12/6/2002 111 5
12/4/2002 121 9

If I use this command:
select min(Date),PrimaaryKey from table3 where (Date<GetDate() ) group by Priority

I'll get two column but I have to only get PrimaryKey column because I want to use in WHERE of UPDATE command

I

Edited by - mazdak on 12/02/2002 15:32:34

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-12-02 : 15:51:38

Use your select statement as a derived table and in the outer select only choose the columns you want ...


select
    a.a
from
    (select
        a,
        b
     from
        abtable) as a

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-02 : 16:09:36

I think this may be what you are asking for, and what Jay was implying you should try:

SELECT Priority, PrimaryKey
FROM
Table3
INNER JOIN
(SELECT Priority, Min(Date) as MinDate FROM Table3 GROUP BY Priority) A
ON A.Priorty = Table3.Priority AND
A.MinDate = Table3.MinDate

- Jeff

Mazdak
Yak Posting Veteran

63 Posts

Posted - 2002-12-03 : 02:18:41

Thanks to both of you.Thats what I want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources