Selecting a Single Record from the Same Multiple Rows from a Single Table

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

How to select multiple rows from the same table only using one query?

A couple of ways:

Using between:

SELECT def_conteudo FROM conteudo WHERE nro_conteudo BETWEEN 101 AND 104;

This fetches every row with an ID between those two number. If I remember correctly, the lower end is inclusive and the higher end is exclusive.

Alternatively, if they are not consecutive:

SELECT def_conteudo FROM conteudo WHERE nro_conteudo IN (101, 102, 103, 104); 

This will fetch the ID's in the list.

How to get multiple rows from single row of table?

You can try this.

Using two query, first query subjectCode by English ,second query subjectCode by English. then use UNION ALL to combine them.

SELECT sub.Col2 as 'Subject',stu.Col4 as 'Marks',stu.Col2 as 'Name'
FROM Students stu
inner join subjects sub on sub.Col1 = stu.Col3
WHERE stu.Col2 = 'Hari'
UNION ALL 
SELECT sub.Col2 as 'Subject',stu.Col6 as 'Marks',stu.Col2 as 'Name'
FROM Students stu
inner join subjects sub on sub.Col1 = stu.Col5
WHERE stu.Col2 = 'Hari'

http://sqlfiddle.com/#!9/513fe5/17

Select one row from multiple rows based on one field

This should get you what you are looking for:

SELECT t1.* FROM award AS t1
   INNER JOIN (SELECT name, MAX(point) AS point
      FROM award
      WHERE country_id IN (0,1)
      GROUP BY name) AS t2
   ON t1.name=t2.name
      AND t1.point=t2.point;

Here is how it breaks down:

This is joining the award table (identified as t1) with a subquery that is identified as t2. The subquery looks like this:

SELECT name, MAX(point) AS point
    FROM award
    WHERE country_id IN (0,1)
    GROUP BY name

The subquery will return the maximum amount of points for each name. Here are the results of the subquery:

+------+-------+
| name | point |
+------+-------+
| A    |   120 |
| B    |    70 |
+------+-------+

Obviously this doesn't include the URL. If it did then URL would have to be in the GROUP BY and then you're entries would be the unique combination of name and URL (and then you wouldn't get the max point value).

The next step is to essentially join to this temporary table to get the rows in the award table that match.

INNER JOIN (...) AS t2
    ON t1.name=t2.name
       AND t1.point=t2.point;

The join finds the rows in the award table where name and point match name and point in the results of the subquery. This gives us the full rows that we want - and we can now access the URL. The result looks like this:

+------+------+------+------------+-------+
| id   | name | URL  | country_id | point |
+------+------+------+------------+-------+
|    1 | A    | a    |          1 |   120 |
|    5 | B    | d    |          1 |    70 |
+------+------+------+------------+-------+

Also, at this point you can order by an column in the award table you want, for example name:

ORDER BY t1.name

or highest point value:

ORDER BY t1.point DESC

How to select a single row where multiple rows exist from a table

Since you have a sequential ID on th_Approved, then I'd use that. Integer comparison on id is perfect. Date/Datetime comparison can sometimes add problems.

So I'd try this:

SELECT Distinct
  Convert(varchar,th_Therapy_Note.th_note_id) as NOTEID,
  '054' as PROGCODE,
  Rtrim(ch.child_caseNumber) as CASEID, 
  Case th_TherapyType.shortname
    when 'ST' then 'SP'
    else           rtrim(th_TherapyType.shortname)
  end as SERVTYPE,
  Convert(varchar,th_Therapy_Note.th_note_dateofservice,101) as DELSERVDATE,
  Cast(((
    Select sum(th_TherapyServiceProvided.units)
    From th_TherapyServiceProvided
    where th_DirectServices.th_ds_id = th_TherapyServiceProvided.th_ds_id)/60) as varchar
  ) as SERVHRS, 
  Cast(((
    Select sum(th_TherapyServiceProvided.units)
    From th_TherapyServiceProvided
    where th_DirectServices.th_ds_id = th_TherapyServiceProvided.th_ds_id)%60) as varchar
  ) as SERVMIN, 
  '1' as METHOD,
   isnull(th_Users.trad_id, ' ') as SPROVNUM,
   th_Users.th_user_lname, '' as COVISIT
FROM th_Therapy_Note
INNER JOIN child_tbl AS ch ON th_Therapy_Note.child_id = ch.child_recordId
INNER JOIN th_DirectServices ON th_Therapy_Note.th_note_id = th_DirectServices.th_note_id INNER JOIN LookUp_contactType ON th_Therapy_Note.contact_type_id = LookUp_contactType.recId INNER JOIN th_Users ON th_Therapy_Note.service_coordinator = th_Users.th_user_email
INNER JOIN th_TherapyType ON th_Therapy_Note.therapy_type = th_TherapyType.id
INNER JOIN th_Approved ON th_Approved.th_approved_id=(
   SELECT MAX(th_approved_id)
   FROM th_Approved
   WHERE th_Therapy_Note.th_note_id = th_Approved.th_note_id)
WHERE ch.child_recordId = (
    SELECT MAX(child_recordId)
    FROM child_tbl
    WHERE child_caseNumber = ch.child_caseNumber)
  AND th_Therapy_Note.th_note_dateofservice > '4/22/2014'
  AND th_Approved.th_approved_isApproved = 1
  AND th_Therapy_Note.th_note_id=16239

---

Related Topics