Grouping results to get unique rows after multiple joins

Question

this is my first question on dba.stackexchange, hopefully it will be worth it. I am usually a bit more active on Stackoverflow as I am a developer.

disclaimer : I don't have full control over the db schema don't judge the data structure or the naming conventions :)

I am doing this large query with multiple joins :

SELECT TOP 30 
iss.iss_lKey as IssueId, 
iss.iss_sName as IssueName, 
con.con_lKey as ContainerId, 
con.con_sName as ContainerName, 
sto.sto_lKey as StoryId, 
sto.sto_sName as StoryName, 
sto.sto_Guid as StoryGuid, 
sto.sto_sByline as Byline, 
sto.sto_created_dWhen as StoryCreatedDate, 
sto.sto_deadline_dWhen as StoryDeadline, 
sto.sto_lType as StoryType, 
sto.sto_sct_lKey as StoryCategory, 
sto.sto_created_use_lKey as CreatedBy, 
sfv.sfv_tValue as FieldValue, 
sf.sfe_lKey as StoryFieldId, 
sf.sfe_sCaption as StoryFieldCaption, 
sre.sre_lIndex as RevisionIndex 
FROM tStory30 sto 
JOIN tContainer30 con ON sto.sto_con_lKey = con.con_lKey 
JOIN tIssue30 iss ON con.con_iss_lKey = iss.iss_lKey 
LEFT OUTER JOIN tStoryRevision30 sre ON sre.sre_sto_lKey = sto.sto_lKey AND sre.sre_lIndex = 0 
LEFT OUTER JOIN tStoryField30 sf ON sre.sre_lKey = sf.sfe_sre_lKey 
LEFT OUTER JOIN tStoryFieldValue30 sfv ON sfv.sfv_sfe_lKey= sf.sfe_lKey 
WHERE sto.sto_sName LIKE '%' + @0 + '%' 
 OR sfv.sfv_tValue LIKE '%' + @0 + '%'

What I need is really only one row by StoryId, that includes the FieldValue that matched if there was any. I am currently grouping in the code to produce the output, but that prevents me from paging.

Is there any way to achieve this kind of grouping in sql, so that I could then page the result properly (using ROW_NUMBER() OVER)?

Also, I am aware that this is bad practice and should use FullText search. it is planned to setup a solr instance, or use the fulltext options in sqlserver. This is a first attempt to get a smthg going.

EDIT: Here verbal description of what I try to achieve.

our app is for magazine editor.

for a given magazine they have many Issues
each issue has many Container (sort of logical article group)
in each container you have several stories
a story van have 0 or many revisions
the fields of a story are stored by revision (many field per revision)
and a field has a field value.

I need to retrieve the stories that have a given text in the name or in a field value of the first revision (that's the where revisionIndex = 0). but I also need to retrieve associated data for each story. (issueId, name, containerId and name, and so one..)

hope this helps!

EDIT Sample data searching for "test". I simplified the columns to make it easier to understand.

Row | IssueId | IssueName | ContainerId | StoryId | FieldValue 
1 | 11 IssueName A 394 868 Test Marsupilami bla bla youpi
2 | 40 IssueName B 6 631 story save test
3 | 40 IssueName B 6 666 test story
4 | 4 IssueName c 30 846 test abs
5 | 4 IssueName c 30 846 absc test
6 | 4 IssueName c 30 846 hello test

I am able to get the row number in sqlserver on my query, but here, as you see, I get amultiple times the same story. In this case, I could have simple the following result:

Row | IssueId | IssueName | ContainerId | StoryId | FieldValue 
1 | 11 IssueName A 394 868 Test Marsupilami bla bla youpi
2 | 40 IssueName B 6 631 story save test
3 | 40 IssueName B 6 666 test story
3 | 4 IssueName c 30 846 test abs

if a story would have test in the story name, then I am ok with a null value in the column FieldValue which field value is selected doesn't matter much.

EDIT ok, so I got a little further. Following is a query I can run, and it will return me exactly the rows I want. Unfortunately, it is missing the column fieldvalue (削除) (which is a ntext and cant be grouped) (削除ここまで) If I could select the longest fieldvalue that matches, that would be perfect.

SELECT ROW_NUMBER() OVER (ORDER BY sto.sto_created_dWhen DESC)
 AS Row,
 iss.iss_lKey as IssueId,
 iss.iss_sName as IssueName,
 con.con_lKey as ContainerId,
 con.con_sName as ContainerName,
 sto.sto_lKey as StoryId, 
 sto.sto_sName as StoryName,
 sto.sto_Guid as StoryGuid,
 sto.sto_sByline as Byline,
 sto.sto_created_dWhen as StoryCreatedDate,
 sto.sto_deadline_dWhen as StoryDeadline,
 sto.sto_lType as StoryType,
 sto.sto_sct_lKey as StoryCategory,
 sto.sto_created_use_lKey as CreatedBy,
 --sfv.sfv_tValue as FieldValue,
 --sf.sfe_lKey as StoryFieldId,
 --sf.sfe_sCaption as StoryFieldCaption, 
 sre.sre_lIndex as RevisionIndex
 FROM tStory30 sto 
JOIN tContainer30 con ON sto.sto_con_lKey = con.con_lKey
JOIN tIssue30 iss ON con.con_iss_lKey = iss.iss_lKey
LEFT OUTER JOIN tStoryRevision30 sre ON sre.sre_sto_lKey = sto.sto_lKey 
 AND sre.sre_lIndex = 0
LEFT OUTER JOIN tStoryField30 sf ON sre.sre_lKey = sf.sfe_sre_lKey 
LEFT OUTER JOIN tStoryFieldValue30 sfv ON sfv.sfv_sfe_lKey = sf.sfe_lKey 
WHERE sto.sto_sName LIKE '%test%'
 OR sfv.sfv_tValue LIKE '%test%'
GROUP BY
 iss.iss_lKey, 
 iss.iss_sName, 
 con.con_lKey, 
 con.con_sName, 
 sto.sto_lKey, 
 sto.sto_sName, 
 sto.sto_Guid, 
 sto.sto_sByline, 
 sto.sto_created_dWhen,
 sto.sto_deadline_dWhen, 
 sto.sto_lType , 
 sto.sto_sct_lKey, 
 sto.sto_created_use_lKey,
 --sfv.sfv_tValue, 
 --sf.sfe_lKey, 
 --sf.sfe_sCaption, 
 sre.sre_lIndex 

• Noam Kremen: Maybe an a short verbal description of the data and the goal you're trying to achieve?
• Andrew Bickerton: Could you give us a sample of the current output you are getting from the query and how you would prefer that output? (the answer could be as simple as using GROUP BY, but I suspect you've got a slightly different goal)
• Stephane: sure it's coming.
• GregW: Your preferred output should also show story 666 too, right? Or if not, why not?
• Stephane: Yes of course, sorry, different storyId :)

Answer 1

If you just want to return one row per story rather than a set, you can use MIN() or MAX() (and a GROUP BY) or SQL Server's TOP 1 function within a subquery to do this. See https://stackoverflow.com/questions/4229931/how-to-write-a-query-to-get-only-first-matching-row-while-joining-two-tables .

Without sample data, and with that complex a query, I don't trust myself to write out bug-free code for you, but you should be able to work it out yourself using the technique described in that article. If not, indicate where you get stuck.

• Stephane: Now about to get some sleep, but I feel that this is the solution. Thanks, will get back tomorrow.
• Andrew Bickerton: A variant of that link is: dba.stackexchange.com/questions/1002/how-to-get-the-max-row/…, @gbn provides a great list of how to get the max/first/last row from your query.

Answer 2

Does it already work in the client-side code, but you just don't have a paging scheme? Assuming your group by in code is done with LINQ, I did a quick search on LINQ and ROW_NUMBER and found a nice how-to on stackoverflow.

https://stackoverflow.com/questions/365086/how-to-project-a-line-number-into-linq-query-results/365127#365127

Hope that helps and please reply if that is not the issue.

• Stephane: Well, my main issue is that I am unable to group on in the sql query. I manage to do it in the code though. but then I cannot page the sql query... I know how to page and I am able to page the current query, but if I do so, I will page BEFORE the grouping, which happens in the code, and my page size gets messed up.

Answer 3

If you need all data from the child rows, then you will always have multiple issueIDs.

Assuming you want to page per issueID, some options:

1. 2 results sets in a stored procedure. Page on the first (IssueID) then "JOIN" back to the 2nd to get detail tows

2. Add a DENSE_RANK() OVER (ORDER BY issueID) to generate a number for paging

• Stephane: I need only 1 relevant field value (when there is any) per story.