I have a table called
Created - datetime
Processed - float (boolean)
Dateprocessed - datetime
Conference - varchar
Session - varchar
FDBue - datetime
Speaker - varchar
Email - varchar
Type - varchar
ID - int
speaker conference session fbdue
Joe Conf1 SessionA 1/1/2010
Conf1 SessionB 1/1/2010
Conf2 SessionE 1/10/2010
Amy Conf1 SessionA 1/3/2010
Conf2 SessionB 1/3/2010
select case when row_number() over ( partition by coalesce(speaker, email) order by conference, session ) = 1 then coalesce(speaker, email) end as speaker, conference, session, fbdue from SpeakerSessions order by coalesce(speaker, email), conference, session;
This problem amounts to knowing which row is the first in a series of rows. That kind of information can't be determined by looking within the row itself.
Looking around at other rows is basically what analytic/window functions are all about.
row_number() is one that applies a numbering to a set of rows in the designated order. The
partition by limits the numbering to each speaker and allows it to reset on the next one. The
order by is also important and much match the
order by of the query results (after
Ultimately the expression says this: if we're at the first row then return the name of the speaker otherwise return a null (blank). Depending on how you plan to use this you may prefer to return an empty string with
else '' instead of the default null.
Many reporting tools have a "suppress repeated values" option to handle this logic for you. It would also be easy to do this in Excel with a formula that looks at the previous cell if you were pulling data into a spreadsheet.