Styjun

What do I need to see before Spider-Man: Far From Home?

Was the 45.9°C temperature in France in June 2019 the highest ever recorded in France?

Taking advantage when HR forgets to communicate the rules

Why does mean tend be more stable in different samples than median?

Wearing special clothes in public while in niddah- isn't this a lack of tznius?

Why would "dead languages" be the only languages that spells could be written in?

Machine Learning Golf: Multiplication

What is the fundamental difference between catching whales and hunting other animals?

Why no parachutes in the Orion AA2 abort test?

Multi-user CRUD: Valid, Problem, or Error?

SIunitx error when using lighter weight

Find max number you can create from an array of numbers

How do I check that users don't write down their passwords?

How to delete multiple process id of a single process?

How would a sea turtle end up on its back?

Any way to meet code with 40.7% or 40.44% conduit fill?

I'm feeling like my character doesn't fit the campaign

How to deal with a Murder Hobo Paladin?

How to factor a fourth degree polynomial

Howto display unicode character u2026 in terminal mode in emacs

Is reasonable to assume that the 食 in 月食/日食 can be interpreted as the sun/moon being "eaten" during an eclipse?

Chilling juice in copper vessel

Why do most airliners have underwing engines, while business jets have rear-mounted engines?

Why do we need a bootloader separate from our application program in microcontrollers?

Exclude one column from group by in a join

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?MySQL - UPDATE query based on SELECT QueryRetrieving the last record in each group - MySQLSelect first row in each GROUP BY group?What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?SQL select only rows with max value on a columnSelect a Column in SQL not in Group ByHow to left join 2 tables with SUM() and MAX() grouped by dateSelecting last record from INNER JOIN and groupingRetireve max/min row with left join trick for a group, when group is on another table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have an aggregated query:

SELECT SERIAL, pos_id, MIN(fecha)
FROM pvs 
WHERE pos_id = 50
GROUP BY SERIAL, pos_id
ORDER BY MIN(fecha) DESC;

and it works quite well to get the information I need, except one piece of data which is the id of that serial number in another table. This happens when I make a join.

SELECT d.id, p.SERIAL, p.pos_id, MIN(fecha)
FROM pvs p
JOIN devices d
ON d.SERIAL=p.SERIAL
WHERE p.pos_id = 50
GROUP BY p.SERIAL, p.pos_id, d.id
ORDER BY MIN(fecha) DESC;

I wanna show the id of every row in the other table but I can't exclude the Id from the group by (I just need the first date and only the first appearance of that serial number). I.e. i want the first query but adding the id from the other table.

mysql greatest-n-per-group

share|improve this question

edited Mar 25 at 20:25

Bill Karwin

394k6767 gold badges531531 silver badges686686 bronze badges

asked Mar 25 at 20:07

ffuentesffuentes

34411 gold badge77 silver badges1717 bronze badges

add a comment | 

0

I have an aggregated query:

SELECT SERIAL, pos_id, MIN(fecha)
FROM pvs 
WHERE pos_id = 50
GROUP BY SERIAL, pos_id
ORDER BY MIN(fecha) DESC;

and it works quite well to get the information I need, except one piece of data which is the id of that serial number in another table. This happens when I make a join.

SELECT d.id, p.SERIAL, p.pos_id, MIN(fecha)
FROM pvs p
JOIN devices d
ON d.SERIAL=p.SERIAL
WHERE p.pos_id = 50
GROUP BY p.SERIAL, p.pos_id, d.id
ORDER BY MIN(fecha) DESC;

I wanna show the id of every row in the other table but I can't exclude the Id from the group by (I just need the first date and only the first appearance of that serial number). I.e. i want the first query but adding the id from the other table.

mysql greatest-n-per-group

share|improve this question

edited Mar 25 at 20:25

Bill Karwin

394k6767 gold badges531531 silver badges686686 bronze badges

asked Mar 25 at 20:07

ffuentesffuentes

34411 gold badge77 silver badges1717 bronze badges

add a comment | 

I have an aggregated query:

SELECT SERIAL, pos_id, MIN(fecha)
FROM pvs 
WHERE pos_id = 50
GROUP BY SERIAL, pos_id
ORDER BY MIN(fecha) DESC;

and it works quite well to get the information I need, except one piece of data which is the id of that serial number in another table. This happens when I make a join.

SELECT d.id, p.SERIAL, p.pos_id, MIN(fecha)
FROM pvs p
JOIN devices d
ON d.SERIAL=p.SERIAL
WHERE p.pos_id = 50
GROUP BY p.SERIAL, p.pos_id, d.id
ORDER BY MIN(fecha) DESC;

I wanna show the id of every row in the other table but I can't exclude the Id from the group by (I just need the first date and only the first appearance of that serial number). I.e. i want the first query but adding the id from the other table.

mysql greatest-n-per-group

share|improve this question

edited Mar 25 at 20:25

Bill Karwin

394k6767 gold badges531531 silver badges686686 bronze badges

asked Mar 25 at 20:07

ffuentesffuentes

34411 gold badge77 silver badges1717 bronze badges

SELECT SERIAL, pos_id, MIN(fecha)
FROM pvs 
WHERE pos_id = 50
GROUP BY SERIAL, pos_id
ORDER BY MIN(fecha) DESC;

SELECT d.id, p.SERIAL, p.pos_id, MIN(fecha)
FROM pvs p
JOIN devices d
ON d.SERIAL=p.SERIAL
WHERE p.pos_id = 50
GROUP BY p.SERIAL, p.pos_id, d.id
ORDER BY MIN(fecha) DESC;

share|improve this question

edited Mar 25 at 20:25

Bill Karwin

394k6767 gold badges531531 silver badges686686 bronze badges

asked Mar 25 at 20:07

ffuentesffuentes

34411 gold badge77 silver badges1717 bronze badges

share|improve this question

edited Mar 25 at 20:25

Bill Karwin

394k6767 gold badges531531 silver badges686686 bronze badges

asked Mar 25 at 20:07

ffuentesffuentes

34411 gold badge77 silver badges1717 bronze badges

edited Mar 25 at 20:25

Bill Karwin

394k6767 gold badges531531 silver badges686686 bronze badges

edited Mar 25 at 20:25

Bill Karwin

394k6767 gold badges531531 silver badges686686 bronze badges

asked Mar 25 at 20:07

ffuentesffuentes

34411 gold badge77 silver badges1717 bronze badges

asked Mar 25 at 20:07

ffuentesffuentes

34411 gold badge77 silver badges1717 bronze badges

1 Answer
1

active

oldest

votes

1

You could join the subquery

select d.id, t.SERIAL, t.pos_id, t.min_fecha
from devices d 
INNER JOIN ( 
 SELECT SERIAL, pos_id, MIN(fecha) min_fecha
 FROM pvs 
 WHERE pos_id = 50
 GROUP BY SERIAL, pos_id
 ORDER BY MIN(fecha) DESC;

 ) t on t.SERIAL = d.SERIAL
ORDER BY min_fecha DESC 

or if you need just one id apply an aggreation funtion

SELECT min(d.id), p.SERIAL, p.pos_id, MIN(fecha)
FROM pvs p
JOIN devices d
ON d.SERIAL=p.SERIAL
WHERE p.pos_id = 50
GROUP BY p.SERIAL, p.pos_id
ORDER BY MIN(fecha) DESC;

share|improve this answer

edited Mar 26 at 6:05

answered Mar 25 at 20:13

scaisEdgescaisEdge

104k1010 gold badges5555 silver badges7373 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I get the same result from the second query.
  
  – ffuentes
  Mar 25 at 20:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you need to add AND d.fecha = t.min_fecha to the join conditions?
  
  – Bill Karwin
  Mar 25 at 20:26
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ffuentes answer updated ..
  
  – scaisEdge
  Mar 25 at 20:29
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The second one works.
  
  – ffuentes
  Mar 25 at 20:34
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55345640%2fexclude-one-column-from-group-by-in-a-join%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

You could join the subquery

select d.id, t.SERIAL, t.pos_id, t.min_fecha
from devices d 
INNER JOIN ( 
 SELECT SERIAL, pos_id, MIN(fecha) min_fecha
 FROM pvs 
 WHERE pos_id = 50
 GROUP BY SERIAL, pos_id
 ORDER BY MIN(fecha) DESC;

 ) t on t.SERIAL = d.SERIAL
ORDER BY min_fecha DESC 

or if you need just one id apply an aggreation funtion

SELECT min(d.id), p.SERIAL, p.pos_id, MIN(fecha)
FROM pvs p
JOIN devices d
ON d.SERIAL=p.SERIAL
WHERE p.pos_id = 50
GROUP BY p.SERIAL, p.pos_id
ORDER BY MIN(fecha) DESC;

share|improve this answer

edited Mar 26 at 6:05

answered Mar 25 at 20:13

scaisEdgescaisEdge

104k1010 gold badges5555 silver badges7373 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I get the same result from the second query.
  
  – ffuentes
  Mar 25 at 20:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you need to add AND d.fecha = t.min_fecha to the join conditions?
  
  – Bill Karwin
  Mar 25 at 20:26
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ffuentes answer updated ..
  
  – scaisEdge
  Mar 25 at 20:29
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The second one works.
  
  – ffuentes
  Mar 25 at 20:34
  

  
  
  
  

add a comment | 

1

You could join the subquery

select d.id, t.SERIAL, t.pos_id, t.min_fecha
from devices d 
INNER JOIN ( 
 SELECT SERIAL, pos_id, MIN(fecha) min_fecha
 FROM pvs 
 WHERE pos_id = 50
 GROUP BY SERIAL, pos_id
 ORDER BY MIN(fecha) DESC;

 ) t on t.SERIAL = d.SERIAL
ORDER BY min_fecha DESC 

or if you need just one id apply an aggreation funtion

SELECT min(d.id), p.SERIAL, p.pos_id, MIN(fecha)
FROM pvs p
JOIN devices d
ON d.SERIAL=p.SERIAL
WHERE p.pos_id = 50
GROUP BY p.SERIAL, p.pos_id
ORDER BY MIN(fecha) DESC;

share|improve this answer

edited Mar 26 at 6:05

answered Mar 25 at 20:13

scaisEdgescaisEdge

104k1010 gold badges5555 silver badges7373 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I get the same result from the second query.
  
  – ffuentes
  Mar 25 at 20:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you need to add AND d.fecha = t.min_fecha to the join conditions?
  
  – Bill Karwin
  Mar 25 at 20:26
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ffuentes answer updated ..
  
  – scaisEdge
  Mar 25 at 20:29
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The second one works.
  
  – ffuentes
  Mar 25 at 20:34
  

  
  
  
  

add a comment | 

You could join the subquery

select d.id, t.SERIAL, t.pos_id, t.min_fecha
from devices d 
INNER JOIN ( 
 SELECT SERIAL, pos_id, MIN(fecha) min_fecha
 FROM pvs 
 WHERE pos_id = 50
 GROUP BY SERIAL, pos_id
 ORDER BY MIN(fecha) DESC;

 ) t on t.SERIAL = d.SERIAL
ORDER BY min_fecha DESC 

or if you need just one id apply an aggreation funtion

SELECT min(d.id), p.SERIAL, p.pos_id, MIN(fecha)
FROM pvs p
JOIN devices d
ON d.SERIAL=p.SERIAL
WHERE p.pos_id = 50
GROUP BY p.SERIAL, p.pos_id
ORDER BY MIN(fecha) DESC;

share|improve this answer

edited Mar 26 at 6:05

answered Mar 25 at 20:13

scaisEdgescaisEdge

104k1010 gold badges5555 silver badges7373 bronze badges

select d.id, t.SERIAL, t.pos_id, t.min_fecha
from devices d 
INNER JOIN ( 
 SELECT SERIAL, pos_id, MIN(fecha) min_fecha
 FROM pvs 
 WHERE pos_id = 50
 GROUP BY SERIAL, pos_id
 ORDER BY MIN(fecha) DESC;

 ) t on t.SERIAL = d.SERIAL
ORDER BY min_fecha DESC 

SELECT min(d.id), p.SERIAL, p.pos_id, MIN(fecha)
FROM pvs p
JOIN devices d
ON d.SERIAL=p.SERIAL
WHERE p.pos_id = 50
GROUP BY p.SERIAL, p.pos_id
ORDER BY MIN(fecha) DESC;

share|improve this answer

edited Mar 26 at 6:05

answered Mar 25 at 20:13

scaisEdgescaisEdge

104k1010 gold badges5555 silver badges7373 bronze badges

answered Mar 25 at 20:13

scaisEdgescaisEdge

104k1010 gold badges5555 silver badges7373 bronze badges

answered Mar 25 at 20:13

scaisEdgescaisEdge

104k1010 gold badges5555 silver badges7373 bronze badges