SQL Ordering by Date but Maintain Foreign Key Groupings

Question

Let's say I have an item table with columns id (PK), parent_id (FK), name, and date. I also have an item_parent table with columns id (PK) and name. Items can belong to the same parent, or an item can have no parent (parent_id can be null).

I am trying to construct a SQL query to select all items ordered by date (DESC), keeping all items that share the same parent together (and in this case, order by the max date of the collection of items).

If it's helpful to know, I'm using SQL Server 2005.

Answer 1

I think this is what you are looking for, as your question is not very clear:

SELECT *
FROM itemTable
ORDER BY parent_id, date desc

This will select all columns and items from the itemTable, with a primary sort on the parent_id and a secondary order by date oldest date first.

Perhaps you can edit your question and post a sample of input data and the required output, to illustrate exactly what you need?

Answer 2

You can use ORDER BY on several columns at once:

SELECT *
FROM itemtable
ORDER BY parentID, Date DESC

Is that what you're looking for?

Answer 3

Since the date you want to sort on is the max date within the group of items for a single parent, the implication is that the items must be sorted first by that date and then, (in the edge case where multiple items with different parents happen to share the same max date), then, within the group defined by the same date secondly, sort by the parent within that group with the same date.

Try this:

   Select i.*
   From item i 
      Left Join item_parent p 
          On p.id = i.parentId
   Order By 
       (Select Max(date) From items
        Where IsNull(parentId,0) = isNull(i.ParentID, 0)) DESC, 
        i.ParentId, i.Date DESC

all the items with no parent will show up together
EDIT: (to fold all items with null parent into the same ordering list...)
2ND EDIT: Just add DESC to Order By -- No offense but you should have seen this!
3RD EDIT: now also sorted within each parent group by actual item date DESC

Answer 4

If you actually want to group by parent_id for those rows that have it defined, then you could use:

select id, parent_id, MAX(date) AS date
from itemTable
group by parent_id, coalesce(parent_id, id)
order by MAX(date) desc

The coalesce allows us to group on ID (i.e. no grouping) only when parent_id is null.

Answer 5

You have a bit of contradiction in how you want to order your items:

select all items ordered by date (DESC), keeping all items that share the same parent together (and in this case, order by the max date of the collection of items).

Does that mean first by parent and then, within a given parent, by date or do you want it first by date and then by parent which means you probably will not get items with the same parent listed together. I assumed the former:

Select I.id, I.parent_id, I.name, I.date
From item As I
    Left Join item As P
        On P.id = I.parent_id
    Outer Apply (
                Select Max(I2.date) As [Date]
                From item As I2
                Where I2.Id = I.parent_id
                ) As MaxDate
Order By I.parent_Id, MaxDate.[Date] Desc, I.Date Desc

If you did not care to return any data from the parent of a given item, then you can eliminate the left join:

Select I.id, I.parent_id, I.name, I.date
From item As I
    Outer Apply (
                Select Max(I2.date) As [Date]
                From item As I2
                Where I2.Id = I.parent_id
                ) As MaxDate
Order By I.parent_Id, MaxDate.[Date] Desc, I.Date Desc