0

I have data that looks like this.

enter image description here

Select distinct 
    x.[PropertyBasics.PK Resnet Property ID], x.filname, 
    c.mv_30day_value '30 Day Value As Is', 
    c.mv_30day_repvalue '30 Day Value Repaired', 
    t.parcel 'Parcel#',x. * 
from 
    Resnet_Reporting_ops.dbo.Ops_FullExportFLATV3 as X (NOLOCK) 
left join 
    resnet_mysql.dbo.form_tax t (nolock) on x.[PropertyBasics.PK Resnet Property ID] = t.property_id
left join 
    resnet_mysql.dbo.form_fm c (nolock) on t.task_id = c.task_id
where 
    X.[PropertyBasics.Property Basics - ResID] = 217 
    and x.[PropertyBasics.PK Resnet Property ID] = 1153829

How do you get this data to only show 1 record for Parcel #?

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
IeeTeY
  • 93
  • 7
  • https://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date/19433107#19433107 Should be able to use the logic there, instead of maxdate use max parcel # – Twelfth Oct 25 '17 at 16:05
  • Which version of SQL? 2008 or 2012, you have both tags. Which parcel are you trying to get? I see some have dashes, I assume you want those removed for this. But your 30 day values aren't the same anyway, so which of those would you want? – kchason Oct 25 '17 at 16:05
  • Explain the logic, we are good but not mind readers. – Juan Carlos Oropeza Oct 25 '17 at 16:08
  • Well If I use maxdate then I have to include a group by clause. In this case I want to retrieve all rows that has a MAX Parcel #. I want the largest Parcel number – IeeTeY Oct 25 '17 at 16:10
  • why not use TOP 1, to display the row – Kashif Qureshi Oct 25 '17 at 16:13
  • Define `MAX Parcel #` because that is a string – Juan Carlos Oropeza Oct 25 '17 at 16:18
  • Please don't use "images of data"! Would you enjoy receiving an answer as the image of the sql query? (How frustrating would all that re-typing be?) – Paul Maxwell Oct 26 '17 at 01:52

1 Answers1

0

select distinct tests the ENTIRE row to see if it is unique. Just the smallest little difference in any column will make a row "distinct". The following 2 values are different, so that will cause 2 rows

  1. 741722064100000
  2. 741-722-06-41-00000

and the following 2 value pairs are different, which cause 2 more rows:

  1. 500000 800000
  2. 435000 850000

in all that combines to make 4 rows.

So, it looks like we could strip the dash from data item 2 above, and that would make it equal data item 1. 

replace(t.parcel,'-','') AS [Parcel#]

But is that always true? Could there be other differences in other rows not shown here?

How do we decide between the value pairs 3. or 4. ? MAX()won't work e.g.

MAX(c.mv_30day_value), MAX(c.mv_30day_repvalue)

would produce

500000 8500000

and that combination doesn't exist in the source data

The logic required to meet the expected result isn't well defined.

Try the following:

SELECT
        x.[PropertyBasics.PK Resnet Property ID]
      , x.filname
      , MAX(c.mv_30day_value)     "30 Day Value As Is"
      , MAX(c.mv_30day_repvalue)  "30 Day Value Repaired"
      , replace(t.parcel,'-','')  "Parcel#"

      -- , x.* NO WAY !!

FROM Resnet_Reporting_ops.dbo.Ops_FullExportFLATV3 AS X
LEFT JOIN resnet_mysql.dbo.form_tax t ON x.[PropertyBasics.PK Resnet Property ID] = t.property_id
LEFT JOIN resnet_mysql.dbo.form_fm c ON t.task_id = c.task_id
WHERE X.[PropertyBasics.Property Basics - ResID] = 217
AND x.[PropertyBasics.PK Resnet Property ID] = 1153829
GROUP BY
        x.[PropertyBasics.PK Resnet Property ID]
      , x.filname
      , replace(t.parcel,'-','')
;

Note. x.* isn't feasible with GROUP BY as you need to specify the columns will define each unique row. x.* is also counter productive with "select distinct" as for each additional column of output you increase the possibility of more rows. (i.e. more columns generally = more differences = more rows). Also as mentioned doubt MAX() produces a good result here.

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51