Can some one help me writing a subquery

Hi Experts,

Can some one help writing a below query:
SELECT * FROM asset_parameter_audit WHERE ID IN(SELECT max(id) FROM asset_parameter_audit WHERE CI_ID=159018 GROUP BY parameter) ;

if i execute query(two individual queries) with out clubbing it is working fine.
when i make it as subquery it is not working.but it not throwing any error.it is not giving result.

Can some one suggest any idea
LVL 2
srikoteshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leon KammerCommented:
Hi Srikotesh,

Nothing is a result.
If the query has clauses that have not been met, then the result is nothing.

Your SQL does not look like it has an error, the only thing I can suggest is to look at the table data and see what is being returned when you run:

SELECT id
FROM  asset_parameter_audit
WHERE CI_ID=159018
Could be there is nothing to return.

If this returns a value, try adding an alias "ID" to the MAX() column in the subquery, also, since you are using MAX(), there will only be one value, so = can be used instead of IN

SELECT *
FROM asset_parameter_audit
WHERE ID = (SELECT max(id) AS "id"
FROM asset_parameter_audit
WHERE CI_ID=159018)
GROUP BY parameter;
F PCommented:
When using subqueries, you'll often run into issues of not being specific enough and the language finds what you wrote to clash with another field. It doesn't know what you really want and especially when you should probably be using an inner join instead of selecting on top of the same table, so try this:

(NOTICE YOU CALL id and ID from the same table? Unless they're different fields, that's wrong.)
SELECT a.* FROM asset_parameter_audit AS a WHERE a.id IN (SELECT max(b.id) FROM asset_parameter_audit AS b WHERE b.CI_ID=159018 GROUP BY b.parameter) ;

Open in new window


Try running your query on a command line and preface it with EXPLAIN.
https://dev.mysql.com/doc/refman/5.0/en/explain.html
srikoteshAuthor Commented:
HI Frank,
I HAVE TRIED UR QUERY BUT I AM NOT GETTING ANY RESULT.

Hi LEON,
i HAVE EXECUTE FIRST IT IS GIVING RESULT.
THEN I HAVE EXECUTED SECOND IT IS ALSO GIVING RESULT.BUT THAT QUERY IS WRONG
SELECT *
FROM asset_parameter_audit
WHERE ID = (SELECT max(id) AS "id"
FROM asset_parameter_audit
WHERE CI_ID=159018)
GROUP BY parameter;
GROUP BY PARAMETER WILL BE INSIDE SUB QUERY.
I HAVE TRIED BY SETTING INSIDE OF SUB QUERY BUT IT IS NOT GIVING RESULT.
SELECT * FROM asset_parameter_audit WHERE ID = (SELECT max(id) AS "id"FROM asset_parameter_audit WHERE CI_ID=159018 GROUP BY parameter);
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

srikoteshAuthor Commented:
whenever my database in hanging while executing query
how can i come out execution of that query. is there any short cut
or i have to open freshly my database connection.
F PCommented:
I think it's not working because of the max function. Try just to ORDER BY id DESC LIMIT 1 instead of putting MAX
F PCommented:
Open up a second console window and use:

SHOW PROCESSLIST;
KILL <thread_to_be_killed>;

Open in new window


That won't kill the connection, only the query.
srikoteshAuthor Commented:
i have to use group by parameter

whenever record update happened one new id will be generate for the same parameter
in that group(parameter)recent update record i have to take means highest id value of that parameter group.

for terminating  hanging query or execution query - i tried with ctrl+c -its working
F PCommented:
You missed what I meant.. and I'm confused what exactly you're trying to get.. Here's what I mean:

SELECT 
  *
FROM 
  asset_parameter_audit
WHERE
  ID = (SELECT max(id) AS "id" FROM asset_parameter_audit WHERE CI_ID=159018)
GROUP BY 
  parameter; 

Open in new window


That query does this:
  1. Gets all fields from the asset_parameter_audit table
  2. Looks for only rows in that table which have the (*case sensitive*) ID field of your sub query. problem is this! ->
    a. 1 record only will be returned in the sub query. EVER. Group by does nothing for you outside of the query.
    b. You ask for a grouping of 1 row.
    c. Your subquery grabs the maximum value of the id field, BUT IT DOES NOT MATTER WHAT THE CL_ID IS AT THAT POINT.

If I understand what you're asking for, you need to rewrite it like this:

SELECT 
  a.*
FROM 
  asset_parameter_audit AS a
WHERE
  a.ID IN (SELECT b.ID FROM asset_parameter_audit AS b WHERE b.CI_ID=159018 ORDER BY b.ID DESC)
GROUP BY 
  a.parameter; 

Open in new window


I think that will give you what you want, but understand = on a WHERE filter says 1 result, and IN  is for multiple. GROUP BY won't do anything with 1 result, and MAX only returns 1 row, and in that case where you just want the top result, you use ORDER BY ID DESC which sorts with max downwards, and put a LIMIT 1 on the query. That should answer your questions and hopefully help you figure it out.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.