sql how to show row with same column value only once

Question

This is my table. I need to write a query that shows all the records but when a project reaches 100% It shows the record for that project only once. so the result should be

USING SQL SERVER 2005

Answer 1

Use NOT EXISTS to return a row if no other row for the same project already (lower date) has reached 100%:

SELECT t1.*
FROM table_name t1
WHERE NOT EXISTS (select 1 from tablename t2
                  where t2.project = t1.project
                    and t2.report_date < t1.report_date
                    and t2.percentage_complete = 100)

Do SELECT DISTINCT to remove duplicate rows (no matter of percentage.)

Answer 2

You could use GROUP BY:

SELECT project, MIN(report_date) AS report_date, percentage
FROM table_name
GROUP BY project, percentage
ORDER BY project, report_date;

MIN will select first date when percentage is the same.

LiveDemo

Output:

╔═════════╦═════════════════════╦════════════╗
║ project ║     report_date     ║ percentage ║
╠═════════╬═════════════════════╬════════════╣
║ ABC     ║ 2016-02-03 00:00:00 ║         45 ║
║ DEF     ║ 2016-02-04 00:00:00 ║         55 ║
║ EFG     ║ 2016-02-05 00:00:00 ║         66 ║
║ EFG     ║ 2016-02-15 00:00:00 ║        100 ║
║ XYZ     ║ 2016-02-06 00:00:00 ║         55 ║
║ XYZ     ║ 2016-02-07 00:00:00 ║         76 ║
║ XYZ     ║ 2016-02-08 00:00:00 ║         87 ║
║ XYZ     ║ 2016-02-09 00:00:00 ║        100 ║
╚═════════╩═════════════════════╩════════════╝

Answer 3

you can use view for doing that

just write the following query for your question

create view view_name as select column_name from table_name where condition;

you have to write the row in the condition for e.g:- where ssid=2

where ssid is the column_name.

Answer 4

You can try a simple distinct. It will hide all repeated rows not only the 100% ones but maybe it will work for you.

SELECT DISTINCT * FROM table_name

Answer 5

Combine GROUP BY and IF:

SELECT * 
FROM  tbale_name 
GROUP BY IF(  percentage = 100, project, NULL ) 

You can add order if necessary.

UPD: instead of null should use unique field:

SELECT * , CONCAT(  `project` ,  `report_date` ,  `percentage` ) AS uniq
FROM  `table_name` 
GROUP BY  IF(`percentage`=100,`project`,`uniq` )
ORDER BY `project`, `report_date`

Answer 6

Try CASE statements, with the use of ROW_NUMBER() to control which record to show the value you want, and when.

Here is an example, where I have a pseudo "primary" key, in a VIEW, but where the value in the table actually repeats itself.

i.e. I want this

    ManifestID  QTY
    A1          200
    A1          null
    A1          null
    A1          null
    A2          50
    A3          23
    A4          78

But not this...

    ManifestID  QTY
    A1          200
    A1          200
    A1          200
    A1          200
    A2          50
    A3          23
    A4          78

SQL

    ALTER VIEW [dbo].[vwEDImanifestLinesRowCount] AS
        SELECT v.ManifestID, 
               CASE WHEN ROW_NUMBER() OVER(PARTITION BY v.VisitID, v.PortID, v.ManifestID ORDER BY v.ManifestID) = 1
                    THEN SPOTmanifest.QTY 
                    ELSE NULL
               END AS [SPOT Manifest QTY]
        FROM   vwEDImanifestLines AS v 
            LEFT JOIN vwEDIinSPOTManifestData AS SPOTmanifest 
                ON SPOTmanifest.ManifestID = v.ManifestID AND SPOTmanifest.VisitID = v.VisitID

So in effect, I am saying, if the ROW_NUMBER() returns 1, for the 1st record, then use the QTY value from my linked table. Otherwise return NULL. So repeating rows are shown, but the QTY is only shown once. You can control this further, for records where you want the 2nd, 3rd, etc row to display the value, and not always the 1st or last record. And no need to GROUP BY.