Hot answers tagged

28

I can see why you're misunderstanding this - it's a little tricky. These are all valid: VARCHAR(1) - one character string VARCHAR(4000) - 4,000 characters VARCHAR(8000) - 8,000 characters - and if you use a number for this field's definition, that's the highest NUMBER you can use, but watch this: VARCHAR(MAX) - that one holds up to 2GB. And yes, if you try ...


13

Your existing query is close to something that you could use but you can get the result easily by making a few changes. By altering your query to use the APPLY operator and implementing CROSS APPLY. This will return the row that meets your requirements. Here's a version that you could use: SELECT u.Username, u.UserType, u.Points, lv.Level FROM ...


9

For SQL Server, you could do something like this. DECLARE @t TABLE (Id INT, DateVal DATETIME, ValueVal DECIMAL(18, 9)); INSERT @t ( Id, DateVal, ValueVal ) SELECT Id, DateVal, ValueVal FROM ( VALUES (12410, '01/03/2017 12:17', 0.000178), (12410, '01/10/2017 11:36', 0.000186), (12410, '01/17/2017 11:27', 0.000189), (12410,...


7

Question 1: How does the 8000 characters come into play and where should I be aware of it? Setting n to 8000 causes 8000 characters to come into play. You need to be aware of the Precision, Scale, and Length (Transact-SQL) references about char, nchar, nvarchar, and varchar. In contrast, setting n to max (no quotes) causes SQL Server to store (and return) ...


6

That should be GREATEST(), not MAX(). MAX() is for aggregating over values from rows, GREATEST() is for aggregating over a list of values (from columns, expressions). You can either find the 3 max values, one for each column and then the greatest of the three: select greatest(max(col1), max(col2), max(col3)) from mytable ; or find the greatest value in ...


4

I am not entirely sure but if the aggregated results are supposed to be per date and the date is production.scheduled_pull_date, then perhaps this: UPDATE pOut SET pOut.lead_ppm = ( SELECT MAX(p1.lead_ppm) FROM dbo.production AS p1 WHERE (p1.tank = 'B' or p1.tank = 'C') AND p1.scheduled_pull_date = pOut....


4

If I understood you correctly, I'd use CROSS APPLY with VALUES to "unpivot" your fields and then a standard MIN and MAX can be used. Something like this: DECLARE @T TABLE (ID int IDENTITY, f1 int, f2 int, f3 int, f4 int); INSERT INTO @T (f1, f2, f3,f4) VALUES (1, 2, 3, 4), (5, 6, 7, 8), (7, 8, 0, 1), (2, 3, 6, 5); SELECT ID, MIN(f) AS MinF, MAX(f) AS MaxF ...


4

Assuming that you want - if the max(number) in the whole table is say, 27 - to update all the rows with NULL, to 28. You need a subquery or a derived table to first find this max and then join back to the table: UPDATE tableX AS t CROSS JOIN ( SELECT MAX(number) AS max_number FROM tableX ) AS m SET t.number = m.max_number + 1 WHERE t....


4

If I understood this correctly, you can use ROW_NUMBER: WITH CTE AS ( SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY fieldA, fieldB ORDER BY fieldC DESC) FROM dbo.YourTable ) SELECT * FROM CTE WHERE RN = 1 ; Here is a demo of this. The results are: ╔════╦════════╦════════╦════════╦════╗ ║ ID ║ fieldA ║ fieldB ║ fieldC ║ RN ║ ╠════╬══...


4

The function is called GREATEST() GREATEST returns the greatest of a list of one or more expressions. Oracle Database uses the first expr to determine the return type. If the first expr is numeric, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before the comparison, ...


3

It looks to me like you don't quite understand how to use GROUP BY. A query with GROUP BY will identify the unique sets of values for the grouped-by columns, and then will allow you to use aggregate functions on any other rows in the data set. So, first you've said, give me the unique values for the four columns in your query. Then, you say, give me the ...


3

Your query is invalid SQL, but MySQL accepts it with the default settings. It will give you a random result. You can change this behavior by adding 'ONLY_FULL_GROUP_BY' to sql_mode. EDIT: Similar to @MickyT's solution but using a join instead: SELECT M.* FROM WEATHER_MEASUREMENT M JOIN ( SELECT MONTH(CREATED) AS MONTH , MIN(AMBIENT_TEMPERATURE) ...


3

The following solution uses a common table expression that scans the Levels table once. In this scan, the "next" points level is found using the LEAD() window function, so you have MinPoints (from the row) and MaxPoints (the next MinPoints for the current UserType). After that, you can simply join the common table expression, lvls, on UserType and the ...


3

Use the GREATEST() function on all the columns SELECT GREATEST (bangla_1st,bangla_2nd,english_1st,english_2nd,math,social_science,religious,science) HighestValue FROM class6A WHERE roll = 4; If any of the columns are NULL, then make the NULL the value 0 SELECT GREATEST ( IFNULL(bangla_1st,0), IFNULL(bangla_2nd,0), IFNULL(english_1st,0), ...


3

I think you want multiple aggregations: First find the max for each ID-Source combination, then sum for every ID: select ID, sum(MaxNumImages) as SumMaxNumImages from ( select ID, max(NumImages) as MaxNumImages from table where Source in ('A','B') and Event = '190' and NumImages is not null group by ID, Source ) as agg group ...


2

You don't need a subquery for this. It can be simple and more efficient: SELECT id_product, date FROM products WHERE fk_stat = 1 AND id_order = 77185 ORDER BY id_product DESC LIMIT 1 ;


2

Do it with a subquery,assuming unique values on id_product SELECT id_product,date FROM products WHERE id_product IN (SELECT MAX(id_product) as maxid FROM products WHERE fk_stat = '1' and id_order = '77185')


2

I think you can use an INNER JOIN -as a performance issue you can also use LEFT JOIN instead- with ROW_NUMBER() function like this: SELECT Username, UserType, Points, Level FROM ( SELECT u.*, l.Level, ROW_NUMBER() OVER (PARTITION BY u.Username ORDER BY l.MinPoints DESC) seq FROM Users u INNER JOIN Levels l ON u.UserType = ...


2

Why not do it using only the rudimentary operations, INNER JOIN, GROUP BY, and MAX: SELECT U1.*, L1.Level FROM Users AS U1 INNER JOIN ( SELECT U2.Username, MAX(L2.MinPoints) AS QualifyingMinPoints FROM Users AS U2 INNER JOIN Levels AS L2 ...


2

If you are using SQL Server, this is a good scenario to use UNPIVOT. You can use write a SELECT to give you the single-row result of SUM values, then apply UNPIVOT to essentially convert that row into a column... and from there you just SELECT TOP 1... Here's an example that gives you the exact results you're looking for: create table #Scores ( name ...


2

It's never going to be pretty, but heres one attempt: select gt, case gt when math then 'Math' when physics then 'Physics' when english then 'English' end from ( select greatest(math, physics, english) as gt , math, english, physics from ( select sum(math) as math , ...


2

If your posts column is BIGINT UNSIGNED then unsigned values have to be 0 or more, so -1 is out of range. MariaDB [test]> desc tbl1; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | id | bigint(20) unsigned | ...


2

You didn't specify your DBMS, so this is ANSI SQL: select sid, num_products from ( select sid, count(*) as num_products, dense_rank() over (order by count(*) desc) as rnk from catalog group by sid ) t where rnk = 1; If you need the information from the supplier table as well, you need to join it: select s.sname, t....


2

One way to accomplish what you're looking for is by using the ROWNUM pseudocolumn. Note that you need to define your ORDER BY in an inline view or by some other method to avoid a common trap that people fall into. Here's one way to do it: SELECT * FROM ( Select S.Student_ID, S.Student_Name, SUM(A.Score) as "TOTAL_SCORE" from Student S ...


2

Create a combined index on snapshot_booking (snapshot_nummer, action_link). Drop the index on snapshot_booking.snapshot_nummer. This will avoid reading the snapshot_booking table, reading the index will be sufficient.


2

This is a clear case to use cte and window functions, please read about them a little. I assume you do not have too much data, so this code is efficient ;with cte as (select Index, cardCode, updateDate, updateTS, unitValue, ROW_NUMBER() OVER(PARTITION BY cardCode ORDER BY updateDate DESC, updateTS DESC) as rn from YourTable) select ...


2

There is no need for subquery/CTE: SELECT TOP 1 WITH TIES * FROM table_name ORDER BY ROW_NUMBER() OVER(PARTITION BY cardCode ORDER BY updateDate DESC, updateTS DESC);


2

SET @@group_concat_max_len = 9; SELECT a.author_name FROM ( SELECT author_id, GROUP_CONCAT(best_seller ORDER BY published_date DESC) AS bs FROM Books GROUP BY author_id HAVING bs = 'true,true' ) AS x JOIN Authors AS a USING(author_id) ; Notes: 9 is based on true,true ...


2

Akina has already pointed out critical problems with the data... that there is no existing way to determine the proper ordering of the Tracking rows. It is tempting to just skip this question, but despite the problems, there are some concepts and SQL queries patterns which can help here. In a traditional relational database, the storage order of records ...


2

This would be as simple as using max() on every column: select max(col1), max(col2), max(col3), max(col4) from the_table; However, due to your wrong data design, you have to cast the varchar values to a number before you can do that: select max(col1::integer), max(col2::integer), max(col3::integer), max(col4) from the_table; Note that this will fail if ...


Only top voted, non community-wiki answers of a minimum length are eligible