CODEWITHSUNDEEP
sql-server
BillyPilgrim
BillyPilgrim

Reputation: 551

Is it possible to do this query without a temp table?

If I have a table of data like this

tableid    author     book                 pubdate
1          1          The Hobbit           1923
2          1          Fellowship           1925
3          2          Foundation Trilogy   1947
4          2          I Robot              1942
5          3          Frankenstein         1889
6          3          Frankenstein 2       1894

Is there a query that would get me the following without having to use a temp table, table variable or cte?

tableid    author     book                 pubdate
1          1          The Hobbit           1923
4          2          I Robot              1942
5          3          Frankenstein         1889

So I want min(ranking) grouping by person and ending up with book for that min(ranking) value.

OK, the data I gave initially was flawed. Instead of a ranking column I'll have a date column. I need the book published earliest by author.

Upvotes: 0

Views: 390

Answers (6)

JeremyDWill
JeremyDWill

Reputation: 3132

Your sample data may be a overly simplistic. You talk about 'min(ranking)', but for all your examples, the minimum ranking for each personid is 1. So the answers you have received so far short-circuit the issue and simple select for ranking = 1. You don't state it in your "requirements", but it sounds like the minimum rank value for any particular personid may not necessarily be 1, correct? Also, you don't mention if a person can rank two or more books with the same minimum rank, so answers will be incomplete due to this missing requirement.

If my psychic abilities are accurate, then you might want to try something like this (untested obviously):

SELECT tableid, personid, book, ranking
FROM UnknownTable UNKTBL INNER JOIN 
     (SELECT personid, min(ranking) as ranking 
      FROM UnknownTable GROUP BY personid) MINRANK
     ON UNKTBL.personid = MINRANK.personid AND UNKTBL.ranking = MINRANK.ranking

This will return all the rows for each person where the ranking value is the minimum value for that person. So if the minimum ranking for person 6 is 2, and there are two books for that person with that ranking, then both book rows will be returned.

If these are not, in fact your requirements, then please edit your question with more details/example data. Thanks!

Edit

Based on your change in requirements/example data, the SQL above should still work, if you change the column names appropriately. You still don't mention if an author can have two books in the same year (i.e. a prolific author such as Stephen King), so the SQL I have here will give multiple rows if the same author publishes two books in the same year, and that year is the earliest year of publication for that author.

Upvotes: 1

dave
dave

Reputation: 1550

WITH min_table as
(
SELECT author, min(pubdate) as min_pubdate
FROM table
GROUP BY author
)
SELECT t.tableid, t.author, t.book, t.pubdate
FROM table t INNER JOIN min_table mt on t.author = mt.author and t.pub_date = mt.min_pubdate

Upvotes: 1

Nick Heidke
Nick Heidke

Reputation: 2847

SELECT tableid,author,book,pubdate FROM my_table as my_table1 WHERE pubdate = 
(SELECT MIN(pubdate) FROM my_table as my_table2 WHERE my_table1.author = my_table2.author);

Upvotes: 1

anon
anon

Reputation:

Missed that a CTE was not valid (but not sure why). How about as a subquery?

SELECT tableid, author, book, pubdate
FROM
(
    SELECT 
        tableid, author, book, pubdate, 
        rn = ROW_NUMBER() OVER 
        (
            PARTITION BY author
            ORDER BY pubdate
        )
    FROM dbo.src -- replace this with the real table name
) AS x
WHERE rn = 1
ORDER BY tableid;

Original:

;WITH x AS
(
    SELECT 
        tableid, author, book, pubdate, 
        rn = ROW_NUMBER() OVER 
        (
            PARTITION BY author
            ORDER BY pubdate
        )
    FROM dbo.src -- replace this with the real table name
)
SELECT tableid, author, book, pubdate
    FROM x
    WHERE rn = 1
    ORDER BY tableid;

If you want to return multiple rows when there is a tie for earliest book, use RANK() in place of ROW_NUMBER(). In the case of a tie and you only want to return one row, you need to add additional tie breaker columns to the ORDER BY within OVER().

Upvotes: 3

therealmitchconnors
therealmitchconnors

Reputation: 2760

select * from table where ranking = 1

EDIT Are you looking for this query to work in situations where there is no value of rank=1 for a given table and person? in that case, try this:

select *, RANK() OVER (Partition By talbeid, personid order by rank asc) as sqlrank 
from table 
where sqlrank = 1

EDIT OF MY EDIT: This will work for the earliest pub date:

select *, RANK() OVER (Partition By author order by pubdate asc) as sqlrank 
from table 
where sqlrank = 1

Upvotes: 1

SeanCannon
SeanCannon

Reputation: 77976

SELECT * FROM my_table WHERE ranking = 1

ZING!

Seriously though I don't follow your question - can you provide a more elaborate or complicated example? I think I'm missing something obvious.

Upvotes: 0

Related Questions