0

I want query in mysql to select a column values which contains Y and N. Below is my table

enter image description here

If I use this query 

"SELECT * from hotel where standard='Y' OR standard='N' group by hotel_code";

enter image description here

This query is working based on insert id but my requirement is not like that, first it should select 'Y' first then only 'N' should come.

[![enter image description here][3]][3]

I want select particular these column values
2 ---- 123 ------Y
4 -----324 ------Y
6 -----456 ------N or 5 ------456 -- N any row from when N appear
7 -----987 ------Y

Thanks in advance!!!

mathi
  • 107
  • 1
  • 11

3 Answers3

1

My previous answer has a problem which indicated error related to only_full_group_by when executing a query in MySql. However, I have created a local database myself and then came up with the correct sql that you need. Here it is. 

SELECT min(origin), hotel_code, max(standard) as std from hotel 
    where standard='Y' OR standard='N' 
    group by hotel_code 
    order by std desc;

And after executing the sql, here's the result that I have got. 

1   123     Y
3   324     Y
7   987     Y
5   456     N

I am sharing the create table and insert statements so that anyone can check by themselves if the query is okay. 

create table hotel (
    origin integer auto_increment primary key,
    hotel_code integer not null, 
    standard varchar(1) not null
);

INSERT INTO `hotel` (`origin`, `hotel_code`, `standard`)
VALUES
    (1, 123, 'Y'),
    (2, 123, 'N'),
    (3, 324, 'N'),
    (4, 324, 'Y'),
    (5, 456, 'N'),
    (6, 456, 'N'),
    (7, 987, 'N'),
    (8, 987, 'Y');

Hope that helps!

Reaz Murshed
  • 23,691
  • 13
  • 78
  • 98
  • simplified your query to: ```SELECT min(origin), hotel_code, max(standart) as std from hotel group by hotel_code order by std desc``` – AndrewShmig Apr 01 '18 at 18:35
  • 1
    Great! Much appreciated. :) – Reaz Murshed Apr 01 '18 at 18:36
  • Great to know that I could help. @AndrewShmig helped me in solving your problem as well. :) – Reaz Murshed Apr 02 '18 at 04:20
  • @Reaz Murshed sorry to ask again, i checked ur query but it is not working. i want particulary select this below origin and hotel_code only (1, 123, 'Y'), (5, 456, 'N'), (4, 324, 'Y'), (8, 987, 'Y') your query is selecting 'Y' but the "ORIGIN" is changing please check and help me to solve .In your result origin mismatch is there. Thanks in advance!!! – mathi Apr 03 '18 at 11:16
0

Here what you need:

SELECT origin, hotel_code, CASE COUNT(DISTINCT standart)
  WHEN 1 AND standart = "N" THEN "N"
  WHEN 1 AND standart = "Y" THEN "Y"
  WHEN 2 THEN "Y"
END as standart
FROM hotel
GROUP BY hotel_code ORDER BY standart DESC

Results:

origin  hotel_code  standart
1   123     Y
3   324     Y
9   888     Y
7   987     Y
6   456     N

SQLFiddle: http://sqlfiddle.com/#!9/17bd53/3/0

AndrewShmig
  • 4,843
  • 6
  • 39
  • 68
  • This does not work. Please fix your sql as this is throwing the following error in my side. `Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hotel.origin' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – Reaz Murshed Apr 01 '18 at 18:29
  • @ReazMurshed, you can open SQLFiddle and check it. works fine. – AndrewShmig Apr 01 '18 at 18:31
  • 1
    Your results are all wrong. Please read the question again carefully. Mathi has asked for an ordering so that the columns for 'Y' values comes first. As stated in the question - **This query is working based on insert id but my requirement is not like that, first it should select 'Y' first then only 'N' should come.** – Reaz Murshed Apr 01 '18 at 18:34
  • @ReazMurshed, yep, forgot to add ORDER BY clause. Thx. – AndrewShmig Apr 01 '18 at 18:38
0

There are many approaches to solve your algorithm, however, due to simplicity I pick this one:

SELECT h2.origin, h2.hotel_code, h2.standard
FROM (SELECT * FROM hotel WHERE standard = 'Y') h1
JOIN hotel h2 on h1.hotel_code = h2.hotel_code
ORDER BY h2.hotel_code, h2.standard;

Click here to view it working

Enjoy it!

Alexandre Almeida
  • 34
  • 2