December 11, 2015 at 6:25 am
in a 3 columns table id , date , page i need a query to extract all distinct dates and the id with min page:
in the following example the result is wrong because for 2001-01-01 the id having min page is 3 but the query returns 1
example:
declare @test-2 table (id int, date datetime, page int)
insert into @test-2 (id, date, page)
values(1,'2001-01-01',3),
(2,'2001-01-01',2),
(3,'2001-01-01',1),
(4,'2001-02-01',1),
(5,'2001-02-01',2),
(6,'2001-03-01',1),
(7,'2001-04-01',1)
Select min(id) as id, date, min(page) as page
from @test-2 group by date
result:
id date page
----------- ----------------------- -----------
1 2001-01-01 00:00:00.000 1
4 2001-02-01 00:00:00.000 1
6 2001-03-01 00:00:00.000 1
7 2001-04-01 00:00:00.000 1
December 13, 2015 at 3:23 pm
The solution works only if (date, page) are a unique key.
If there are 2 or more records with the same (date, page) values you'll be getting randomly selected id from those records.
_____________
Code for TallyGenerator
December 14, 2015 at 3:39 am
yes it's a newspaper, there's only one page for one day.
December 14, 2015 at 5:26 am
fabriziodb (12/14/2015)
yes it's a newspaper, there's only one page for one day.
And there is always page 1. Which is always min page number.
Correct?
_____________
Code for TallyGenerator
December 14, 2015 at 6:08 am
no, the data comes from a newspaper's supplement , first page is variable.
December 14, 2015 at 1:38 pm
fabriziodb (12/11/2015)
in a 3 columns table id , date , page i need a query to extract all distinct dates and the id with min page:in the following example the result is wrong because for 2001-01-01 the id having min page is 3 but the query returns 1
example:
declare @test-2 table (id int, date datetime, page int)
insert into @test-2 (id, date, page)
values(1,'2001-01-01',3),
(2,'2001-01-01',2),
(3,'2001-01-01',1),
(4,'2001-02-01',1),
(5,'2001-02-01',2),
(6,'2001-03-01',1),
(7,'2001-04-01',1)
Select min(id) as id, date, min(page) as page
from @test-2 group by date
result:
id date page
----------- ----------------------- -----------
1 2001-01-01 00:00:00.000 1
4 2001-02-01 00:00:00.000 1
6 2001-03-01 00:00:00.000 1
7 2001-04-01 00:00:00.000 1
First, I realize that this is example code (I hope) but your column names are all reserved words which is really bad thing to do.
Second, is this what you are trying to accomplish:
declare @test-2 table (id int, date datetime, page int)
insert into @test-2 (id, date, page)
values(1,'2001-01-01',3),
(2,'2001-01-01',2),
(3,'2001-01-01',1),
(4,'2001-02-01',1),
(5,'2001-02-01',2),
(6,'2001-03-01',1),
(7,'2001-04-01',1);
with basedata as (
select
id,
date,
page,
rn = row_number() over (partition by date order by id asc)
from
)
select
id,
date,
page
from
basedata
where
rn = 1;
December 14, 2015 at 2:08 pm
Lynn Pettis (12/14/2015)
rn = row_number() over (partition by date order by id asc)
The way I read the ops question was for the id with the min page number, so shouldn't it be:
rn = row_number() over (partition by date order by page asc)
Edit to fix misspelling.
Don Simpson
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply