Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I've modified my solution to take into account the extra information added in the question. The below query will get you the second sort priority you specified. In order to get the first one, you'd change the "max" in the outer apply to "min" and change the "sortOrder desc" to "sortOrder asc". Keep in mind if you have multiple ties for most frequent, say A,A,B,B,C and A came first, it would go with B in the below code because that was the highest count and came after the 2 A's.</p> <pre><code>-- setup test table create table ExcelDump( id int , Col1 char(1) , Col2 char(1) , Col3 char(1) ) insert into ExcelDump values(1,null,null,'C') insert into ExcelDump values(1,null,'B','C') insert into ExcelDump values(1,'A','B','D') insert into ExcelDump values(1,'E','B','C') insert into ExcelDump values(2,'A','B','C') insert into ExcelDump values(2,'A','B','C') insert into ExcelDump values(3,'A','B','C') insert into ExcelDump values(3,'A','B','F') insert into ExcelDump values(4,'A','B','C') insert into ExcelDump values(4,'G','B','C') -- create temp tables to make it easier to debug select distinct id into #distinct from ExcelDump -- number order isn't guaranteed but should be sorting them as first come first serve from the original table if no indexes exist select row_number() over(order by (select 1)) as numberOrder , ID , Col1 , Col2 , Col3 into #sorted from ExcelDump -- actual query select ui.Id , col1.Col1 , col2.Col2 , col3.Col3 from #distinct ui outer apply ( select top 1 ed.Col1 , count(*) as cnt , max(ed.numberOrder) as sortOrder from #sorted ed where ed.id = ui.id and ed.Col1 is not null -- ignore nulls group by ed.Col1 order by cnt desc, sortOrder desc -- get most common value, then get last one found if there are multiple ) col1 outer apply ( select top 1 ed.Col2 , count(*) as cnt , max(ed.numberOrder) as sortOrder from #sorted ed where ed.id = ui.id and ed.Col2 is not null -- ignore nulls group by ed.Col2 order by cnt desc, sortOrder desc -- get most common value, then get last one found if there are multiple ) col2 outer apply ( select top 1 ed.Col3 , count(*) as cnt , max(ed.numberOrder) as sortOrder from #sorted ed where ed.id = ui.id and ed.Col3 is not null -- ignore nulls group by ed.Col3 order by cnt desc, sortOrder desc -- get most common value, then get last one found if there are multiple ) col3 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POTransform raw data into relational data
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USZhenny
    1. USZhenny
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POTransform raw data into relational data
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThis looks something I might be able to use. Performance isn't an issue as this would be the transform step in ETL. I updated the question with a bit more detail, would you still say this is the best approach?
      singulars
      1. PO
      1. USSnæbjørn
    2. COI've modified the answer I gave to take into account your updated question. I wouldn't say this is necessarily the "best" approach as there is more than one way to skin a cat. It is simply a way that will work.
      singulars
      1. PO
      1. USZhenny
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload