How to select rows where multiple columns have same values

Question

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.

D-Shih · Accepted Answer · 2018-08-17 21:23:18Z

3

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

Results:

| 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 |

edited Aug 17, 2018 at 21:23

answered Aug 17, 2018 at 21:08

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vamsi Prabhala Over a year ago

your previous answer would've been sufficient.

star_kid Over a year ago

Do we need the final Order BY statement at all?

sticky bit · Accepted Answer · 2018-08-17 22:12:09Z

2

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.

edited Aug 17, 2018 at 22:12

answered Aug 17, 2018 at 21:19

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

4 Comments

star_kid Over a year ago

Wondering why it never occurred to me. In case I want to select the row with same bookid, pageid and textid with the most recent date?

Gordon Linoff Over a year ago

@star_kid . . . This seems like the best answer to the question.

sticky bit Over a year ago

@GordonLinoff: Not sure about the DESC. If you look at the desired results it seems to be ASC. May depend on whether you emphasize "most recent" or "ascending" in "on the most recent date ascending"...

Gordon Linoff Over a year ago

@stickybit . . . I was responding to the comment above.

Collectives™ on Stack Overflow

How to select rows where multiple columns have same values

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related