spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

MS-Access SELECT TOP N subquery SQL


Try this :

SELECT  c.SubTypeName,
        c.StationId,
        c.Value

FROM [Sample] AS c
WHERE c.SubTypeId IN

            (SELECT TOP 2 c2.SubTypeId 
             FROM  [Sample] As c2 
             WHERE c2.StationId = c.StationId
             ORDER BY c2.Value DESC, c2.subtypeid)

ORDER BY c.StationId ASC, c.Value DESC

I'm not sure if this solution will affect any other records in your Db (because you are dealing with more than I), but working with the sample data you provided this seemed to work.


Categories : SQL

Related to : MS-Access SELECT TOP N subquery SQL
Trouble with SELECT WHERE NOT IS/EXISTS Subquery (VB.NET, Access)
I think something is wrong here WHERE @startDate <= bookingEndDate AND bookingStartDate <= @endDate try changing it to WHERE bookingStartDate >= @startDate AND bookingEndDate <= @endDate also put in mind to put ".Date" in your parameters.. unbookedLocationsCommand.Parameters.AddWithValue("startDate", dtpStartDate.Value.Date)

Categories : SQL
can a SELECT Column value be used in a subquery?
Give Alias name and use it inside correlated Subquery. SELECT HeaderID, HeaderName, HeaderFileName, CreatedDate, UsageCounts =(SELECT Count(ClassXML.value('(/Data/prpID)[1]', 'INT')) FROM CustomContent A WHERE ClassXML.value('(/Data/prpID)[1]', 'INT') = H.HeaderID) FROM Headers H

Categories : Sql Server
Speed up SYS_* table access created during WITH subquery
In order to use index scan you have to create it first. If you want to speed up your query, rewrite it without WITH data AS, because WITH takes a lot of Oracle resources. Here's how: select * from t2 where not exists (select 1 from t2 where object_id between 1 and 100000 and object_id = t2.object_id);

Categories : SQL
Syntax for MySQL SELECT statement with a subquery within the IF statement
You need a something before the subselect: SELECT * FROM Products WHERE Color = 'Black' AND IF (Item = 'Dresses' ?? (SELECT * FROM Products WHERE Price < '$300'), 1, 0) = 1 --------------------------^ I am not quite sure what, because there are many other problems, such as: Using * where a scalar subquery is expected. Using '$300' to presumably compare against a number. (Or worse, st

Categories : Mysql
How to change subQuery to Co-related Subquery?
I think this is what you're looking for. Just move the query into the FROM section and use a JOIN to filter the results based on the same conditions you had in the WHERE clause, except this time you reference the result set (RS) returned by the subquery. SELECT FirstName, LastName FROM AdventureWorks.Person.Contact a INNER JOIN [AdventureWorks].[HumanResources].[Employee] b ON a.ContactID

Categories : SQL
Recently Add
Use REGEXP_REPLACE to replace variable number of characters with same number of constant characters
How to subtract a data with nano-second in a same group
listagg alternative in Oracle
How to join 3 tables to a single table in SQL
Postgresql: Trying to Understand the Information Schema Table Constraints Info for a NOT NULL Contstraint
Data Frame in R use like SQL, possibly using sqldf()
How can I group by a table by the date?
Query slows down in Conditional IF
Getting the daily sales report given the date
Retrieve rows with unique values in a column
case sensitive sql search in vb.net
Oracle multi dimensional query
Update column when datediff is greater then other column in the same table
add value from different tables and insert total value into another table
How to prevent transaction locking in sql server for all the connections?
Check the query efficiency
how can I run this select statement inside a select statement without any problems?
Add amount once on duplicate entries
SQL Server 2012 - Unique Priority Column
Query: employees who do not have the same name
Trigger to not allow a member to rent if unpaid balance exceeds $50
Anyway way to change this Oracle SQL statment to use regs?
If you set a field name as an Alias, can you utilize that name within SQL code?
Increment next month with dayOfTheMonth SQL sybase
Update Table Set From is not working as expected
How do I find one matching strings in two txt files
SQL help needed (oracle application express)
How to Roll Up Weekending Data in SQL
How do you call a variable (that is a list of values) in a conditional statement?
Oracle INSTR backward in Oracle SQL
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.