sql query first rows with specific column values

i want to start by saying that i know there are a couple of questions regarding similar problems but they either dont answer my question fully or seem to be incompatible with SQLite.

I want to query all rows with value -1 and the first rows with values other than -1. And by "first rows" i mean the group of rows that are first with a certain value. the first row is the row that is first stumbled upon depending on the SORT BY clause

An example of the data and outcome:

Data:

a b -1 c d 1 e f 2 g h 2 i j 2 k l -1

Result:

a b -1 c d 1 e f 2 k l -1

And as said above, i am using a SQLite database

-------------Problems Reply------------

Do this as two separate queries, one of them containing an inline view; UNION the two

select blah, blah from T where ...
UNION
select * from
(
select blah, blah from T where something else order by somecolumn limit 1
)

With simple example:

> select * from ex1;
+------+----------+
| id | name |
+------+----------+
| 1 | Pirate |
| 2 | Monkey |
| 3 | Ninja |
| 4 | Spagheti |
| 5 | kumar |
| 6 | siva |
+------+----------+

> select * from ex1 union select "1", "sing" order by case name when 'sing' then 1 else 2 end, name;
+------+----------+
| id | name |
+------+----------+
| 1 | sing |
| 5 | kumar |
| 2 | Monkey |
| 3 | Ninja |
| 1 | Pirate |
| 6 | siva |
| 4 | Spagheti |
+------+----------+

Category:sql Views:0 Time:2011-08-29
Tags: sql sqlite

Related post

  • SQL query: extract rows having specific course only 2011-07-28

    I have a situation. How can I extract AppId that has only taken C++. and no other course. Sample data is shown below AppID | Course --------------- 101 | C++ 101 | Data Structure 100 | C++. 201 | Analysis of Alogrithm 201 | Data Structure and so on H

  • sql query to split a single column value into multiple rows 2011-04-26

    I want to create a sql query to split a single column value into multiple rows like: SELECT ID, PRODUCT_COUNT FROM MERCHANT WHERE ID = 3050 ID PRODUCT_COUNT ----------- ------------- 3050 591 Based on this result, I want 6 rows as follows: ID RANGE 3

  • SQL Query to add a new column after a existing column in SQL Server 2005 2011-01-19

    I need a SQL query which add a new column after an existing column, so the column will be added in a specific order. Please suggest me if any ALTER query which do that. --------------Solutions------------- ALTER won't do it because column order does

  • Select all row for specific column query SQL Server 2008 2010-08-17

    i want to know what is the wrong in this query I want to select all rows for 1 column string command = "select money from User_Data"; SqlCommand update_money = new SqlCommand(command, con_string.con); SqlDataReader money_reader; money_reader = update

  • SQL select first records of rows for specific column 2011-02-03

    I realize my title probably doesnt explain my situation very well, but I honestly have no idea how to word this. I am using SQL to access a DB2 database. Using my screenshot image 1 below as a reference: column 1 has three instances of "U11124", with

  • SQL Query Needed ( based on the column value, row value should be loaded ) 2011-09-30

    My Sql query is, Select CustomerName,ProjectID,ProjectDesc from dbo.projects The Output is: CustomerName ProjectID ProjectDesc A3 Consulting FZ, LLC. 277 A3 A3 Consulting FZ, LLC. 278 A3 - Chef Anesthesia Business Consultant 279 Astra ARI Network Ser

  • optional search parameters in sql query and rows with null values 2010-03-12

    Ok here is my problem : Before i start the description, let me to tell you that I have googled up a lot and I am posting this question for a good optimal solution :) i am building a rest service on WCF to get userProfiles... the user can filter userP

  • messaging an existing sql query to include a count column 2011-01-11

    I have a large SQL query that returns a classic asp ADO.Recordset. I want to add a column to the query that simply contains a count of all the distinct PublicationID's along with all the other data, how to? Below is just a sample as I don't want to p

  • Exclude rows with specific columns values from SELECT 2014-06-03

    I've got table with following columns and data: stock quant ----- ----- 10 0 10 -5 10 1 1 20 10 1 10 88 5 1 What I need is to exclude rows with stock_status = 10 and quantity <= 0 at the same time. I need to have those rows with stock_status = 10

  • SQL Query to checks on the column that always continue 2015-01-11

    I have a table named GRADE and has columns GRADE_NAME, POIN_FROM, POIN_TO. GRADE table looks like this : GRADE_NAME POIN_MIN POIN_MAX A 90 100 B 75 89 C 50 69 D 30 49 E 10 29 F 0 10 Now, i want to write an sql query to check that all values in column

  • Print last 10 rows of specific columns using awk 2009-07-17

    I have the below awk command-line argument and it works aside from the fact it performs the print argument on the entire file (as expected). I would like it to just perform the formatting on the last 10 lines of the file (or any arbitrary number). An

  • Howcome my SQL query is not sorting the column? 2010-02-07

    http://new.monmouthchineseschool.com/curriculum/cantonese/textbook.php if you take a look at 'C1' 一 十 十一 十二 十三 二 三 四 五 六 七 八 九 that is how all the links look like. this is the mysql db for that class CREATE TABLE IF NOT EXISTS `mon_textbook` ( `id` i

  • SQL Server - need a SQL Query to identify/highlight specific changes in an audit table 2010-06-22

    Say I have some data stored in an audit table, where triggers on the main data table write all invoice record updates to this audit table. The audit table contains this data: InvoiceID CustomerID ItemSold AmountSold SalesPerson ModifyDate 1001 96 Wid

  • SQL Query multiple values of a column as multiple columns (SQL 2005) 2010-11-25

    When I run a SQL query on a single table and here is the data (this is just a sample, error column might be more than 10) time total Error 00:16 6 10000(E) 00:20 4 10000(E) 00:46 2 10000(E) 01:01 2 10000(E) 01:40 2 10000(E) 02:07 2 10000(E) 02:52 1 1

  • oracle sql query error while comparing date columns 2010-12-14

    I have a following oracle sql query in which START_DATE is a number column and a_date is DATE type and the input value is also of type DATE. kindly let me know how to compare the date columns with the input date. select a.id ,a.v ,b.id,b.v from DATA

  • Filtering SQL query by row and by date range 2010-12-14

    I have a time indexed Oracle DB which I'm trying to query by date range. I also want to do data reduction in the query so I don't get overwhelmed with too much data. The stand alone date query (2352 rows in 0.203s): select oracle_time from t_ssv_soh_

  • SQL query use WHERE on a column ALIAS 2011-03-18

    This should be easy but I'm getting an "Invalid column Name" error. SELECT Transaction, COUNT(ItemId) AS ItemCount FROM TransactionTable WHERE ItemCount > 5 GROUP BY Transaction ORDER BY ItemCount DESC This statement works without the WHERE clause

  • SQL - Query only the first 25 columns in that table 2011-09-21

    75 columns in a table - I want to query only the first 25 columns in that table without naming each column name.... can you assist with a SQL query.... I been playing with the following: Select Table_Name, Count(*) As ColumnCount From Information_Sch

  • SQL Query Speed When Table has Column of Type "Text" 2011-10-10

    I am designing an SQL database (accessed via PHP/MySQL) and have questions about designing the database in a way that helps the website run relatively quickly. My precise question pertains to speed when querying a table with many columns where one co

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.220 (s). 11 q(s)