Reputation: 191
I have a sql server table named myTbl like this:
BookID PageID textID Date Remarks
1 4 9 21-12-2017 1
2 5 10 15-12-2017 1
3 6 11 13-12-2017 1
4 7 12 11-12-2017 1
2 5 10 22-12-2017 1
4 7 12 18-12-2017 1
I want to group the rows when BookID, PageID and textID have same values and show the result based on the most recent date ascending. For instance 4th & 6th row and 2nd & 5th row.
What I need is:
BookID PageID textID Date Remarks
1 4 9 21-12-2017 1
2 5 10 15-12-2017 1
2 5 10 22-12-2017 1
3 6 11 13-12-2017 1
4 7 12 11-12-2017 1
4 7 12 18-12-2017 1
How I want to write is:
SELECT *
FROM booksdb.dbo.books
GROUP BY BookID, PageID, textID Order by Date
I want all the columns in the table where BookID, PageID, textID are same and arranged by date.
Upvotes: 0
Views: 1371
Reputation: 37487
All you seem to need is an ORDER BY
all the columns you mentioned in the order you mentioned them.
SELECT *
FROM booksdb.dbo.books
ORDER BY bookid,
pageid,
textid,
date;
Edit:
Note: If you want the order of the dates to show the youngest one first (e.g. 18-12-2017 before 11-12-2017), rather than the other way around, change the ORDER BY
clause to:
ORDER BY bookid,
pageid,
textid,
date DESC;
(Note the DESC
after date
).
The desired result may contradict the description you gave and overall they leave room for interpretation regarding this point.
Upvotes: 2
Reputation: 46249
You don't need group by
, Because there is not any aggregate function in your query.
you can try to use RANK
with window function to make row_number then order by the number.
CREATE TABLE T (
BookID INT,
PageID INT,
textID INT,
DATE DATE,
Remarks INT
);
INSERT INTO T VALUES (1,4,9 ,'2017-12-21',1);
INSERT INTO T VALUES (2,5,10,'2017-12-15',1);
INSERT INTO T VALUES (2,5,10,'2017-12-22',1);
INSERT INTO T VALUES (3,6,11,'2017-12-13',1);
INSERT INTO T VALUES (4,7,12,'2017-12-11',1);
INSERT INTO T VALUES (4,7,12,'2017-12-18',1);
Query 1:
;WITH CTE AS (
SELECT *,RANK() OVER(PARTITION BY BookID, PageID, textID Order by Date) rn
FROM T
)
SELECT BookID,PageID,textID,[DATE],Remarks
FROM CTE
ORDER BY BookID, PageID, textID,rn
| BookID | PageID | textID | DATE | Remarks |
|--------|--------|--------|------------|---------|
| 1 | 4 | 9 | 2017-12-21 | 1 |
| 2 | 5 | 10 | 2017-12-15 | 1 |
| 2 | 5 | 10 | 2017-12-22 | 1 |
| 3 | 6 | 11 | 2017-12-13 | 1 |
| 4 | 7 | 12 | 2017-12-11 | 1 |
| 4 | 7 | 12 | 2017-12-18 | 1 |
Upvotes: 3