Select Multiple Rows with the Same Value(S)

Select multiple rows with the same value(s)

You need to understand that when you include GROUP BY in your query you are telling SQL to combine rows. you will get one row per unique Locus value. The Having then filters those groups. Usually you specify an aggergate function in the select list like:

--show how many of each Locus there is
SELECT COUNT(*),Locus FROM Genes GROUP BY Locus

--only show the groups that have more than one row in them
SELECT COUNT(*),Locus FROM Genes GROUP BY Locus HAVING COUNT(*)>1

--to just display all the rows for your condition, don't use GROUP BY or HAVING
SELECT * FROM Genes WHERE Locus = '3' AND Chromosome = '10'

How to select where multiple rows have the same values in two columns, respectively?

If you only want the team names (and not the names of the duplicate players), then one method is to use SELECT DISTINCT with a GROUP BY:

SELECT DISTINCT Team
FROM TeamPlayer
GROUP BY Team, Player
HAVING COUNT(*) > 1;

I mention that because SELECT DISTINCT in an aggregation query is almost always a mistake, but this is one valid use.

That said, I would might be more inclined to do:

SELECT Team
FROM TeamPlayer
GROUP BY Team
HAVING COUNT(*) > COUNT(DISTINCT Player);

How to select a single row from an sql table with multiple rows having same value in a column

you can write required query like below if you have a column date_edited with type TIMESTAMP .

SET @@sql_mode='ONLY_FULL_GROUP_BY';
SELECT Name, Age, 
GROUP_CONCAT(DISTINCT City ORDER BY date_edited DESC) AS city 
FROM TABLE_NAME 
GROUP BY City
ORDER BY date_edited DESC

Find row that has same value in one column over multiple rows while another column has different values

You can use group by clause with min() and max() :

select col1
from table t
group by col1
having min(col2) <> max(col2);

Select rows with same id and set same value in a row

Maybe try using max() :

select ID,
max(NAME) over(partition by REKEY) as LIEFNR,
REKEY
from table_name;

SQL Select multiple rows with same column values

Since you are using SQL Server you can use ranking functions to get the result:

select [column1], [column2], [column3], [column4]
from
(
  select [column1], [column2], [column3], [column4],
    row_number() over(partition by column2, column3, column4 order by column1) rn
  from yourtable
) src
where rn <= 3

See SQL Fiddle with Demo

Select Multiple Rows Based On Common Column Value Oracle SQL

You can use not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.ref_id = t.ref_id and t2.active = 1
                 );

Or window functions:

select t.*
from (select t.*,
             max(active) over (partition by ref_id) as max_active
      from t
     ) t
where max_active = 0;

CASE WHEN - Multiple Conditions - Over Multiple Rows of same reference

You need a window function (an aggregation function with an OVER clause) to look at multiple rows at once.

SELECT 
  "o/n", sku, order_type, state,
  CASE
    WHEN order_type <> 'Grouped' THEN
      'single_state'
    WHEN COUNT(DISTINCT state) OVER (PARTITION BY "o/n", order_type) = 1 THEN
      'single_state'
    ELSE
      'multi_state'
  END
FROM data.table
ORDER BY "o/n", sku;

---

Related Topics