Anything like GROUP BY that gives you the max value'd field instead of the min?

Question

I'm trying to get the results from this question (SO 2429348 - Help with a SQL query) except I need it to return values 1 and 3.

Edit: GROUP BY loses all except the first unique value, so replacing min with max will not work.

Edit: I didn't want to show what I'm working with but I guess I will: Here is the table called 'test_posts'

ID      smallint(6)             
Thread  smallint(6) 
Board   smallint(6) 
post    text    utf8_general_ci         
name    text    utf8_general_ci         
trip    text    utf8_general_ci         
ip  text    utf8_general_ci         
Date    datetime 

I'm trying to set up a query that shows the first post of each thread and is ordered by the date of the last post in each thread. I got the first part down with this query:

SELECT *
FROM (
SELECT Min( ID ) AS MinID
FROM test_posts
GROUP BY Thread
)tmin

JOIN test_posts ON test_posts.ID = tmin.MinID 

Now I need to figure out how to call the last post of each thread into a table, than use that table to order the first tables results.

Answer 1

Substitute MAX for MIN?

Answer 2

Here is a refined table schema. Primarily, I'm guessing that ID is the PK.

Create Table test_posts
(
    ID int not null PRIMARY KEY
    , Thread int not null 
    , Board int not null 
    , post varchar(100) null
    , name varchar(100) null
    , trip varchar(100) null
    , ip varchar(15) null
    , Date datetime null
)

How is "last post of each thread" defined? I'm assuming that "first" and "last" are being defined on the ID which is presumed to be sequential or should it be defined on [Date]? What DBMS is being used? That will dictate what features we can use in the answer. Having some sample output would also help significantly.

With all that said, here's a stab at it:

;With LastPosts
    (
    Select T.Id, T.Thread, T.Board, T.Post, T.Name, T.Trip, T.IP, T.Date
    From test_posts As T
    Where T.Id =    (
                    Select Max(T2.Id)
                    From test_posts As T2
                    Where T2.Thread = T.Thread
                    )
    )
Select T.Id, T.Thread, T.Board, T.Post, T.Name, T.Trip, T.IP, T.Date
    , LastPosts.[Date]
From test_posts As T
    Join LastPosts
        On LastPosts.Thread = T.Thread
Where T.Id =    (
                Select Min(T2.Id)
                From test_posts As T2
                Where T2.Thread = T.Thread
                )
Order By LastPosts.[Date]

Answer 3

William,

If your implementation of SQL supports RANK() and windowed aggregates, this should be easy. Using Thomas's table definition, it might look something like this:

with test_posts_ranked as (
  select
    *,
    rank() over (
      partition by Thread, Board
      order by "Date" asc
    ) as rk_forward,
    max("Date") over (
      partition by Thread, Board
    ) as last_post_date_this_thread_board
  from test_posts
)
  select
    ID, Thread, Board, post, name, trip, ip, Date
  from test_posts_ranked
  where rk_forward = 1
  order by last_post_date_this_thread_board

Answer 4

A word of advice: This is one of those times when you're going to want to denormalize.

Look at the solutions here and you'll see why it's difficult to write the query. Try running some of the solutions and you'll see how poor the performance can be.

(Note - this doesn't make those solutions any less valuable, they look correct and I am not trying to detract from that; what I'm saying is that they solve the problem you asked but not the problem that you ought to be solving).

Every piece of forum software I know of denormalizes the "last post date" field because the main forum display (index of posts) is almost always sorted by this field and really needs to be displayed fast. If a forum has a million posts then your users will be taking bathroom breaks while they wait for your forum page to load.

It's rare that I tell people to denormalize - normally I'm busy explaining how to normalize - but trust me on this one (or don't - profile it!) - you'll want to keep a LastPostDate and possibly a LastPostUser field in the Threads table (assuming you have a Threads table...). That way the query becomes a simple ORDER BY LastPostDate DESC and you can index this column for fast performance.

The number of posts on a forum is an order of magnitude, often two orders of magnitude higher than the number of threads; you do not want to be diving into the Post table/index just to display the list of new threads.