To start with you can’t do this:
having rid!=MAX(rid)
The HAVING clause can only contain things which are attributes of the aggregate groups.
In addition, 1, 2, 3
is not valid in GROUP BY in SQL Server – I think that’s only valid in ORDER BY.
Can you explain why this isn’t what you are looking for:
select LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1), qvalues.name, qvalues.compound, MAX(qvalues.rid) from batchinfo join qvalues on batchinfo.rowid=qvalues.rowid where LEN(datapath)>4 group by LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1), qvalues.name, qvalues.compound
Related Posts:
- Selecting COUNT(*) with DISTINCT
- What is a stored procedure?
- Self Join to get employee manager name
- How do I UPDATE from a SELECT in SQL Server?
- SQL query to select dates between two dates
- When should I use CROSS APPLY over INNER JOIN?
- How can I do an UPDATE statement with JOIN in SQL Server?
- How do I escape a single quote in SQL Server?
- T-SQL split string based on delimiter
- How do I escape a single quote in SQL Server?
- IDENTITY_INSERT is set to OFF – How to turn it ON?
- Column name or number of supplied values does not match table definition
- SQL Server FOR EACH Loop
- DateTime2 vs DateTime in SQL Server
- How to parse XML data in SQL server table
- What is the equivalent of ‘describe table’ in SQL Server?
- Error converting data type varchar to float
- What is the Oracle equivalent of SQL Server’s IsNull() function?
- I want to use CASE statement to update some records in sql server 2005
- Conversion failed when converting the varchar value ‘simple, ‘ to data type int
- Must declare the scalar variable
- SELECT DISTINCT on one column
- Exclude a column using SELECT * [except columnA] FROM tableA?
- Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (Year)
- TSQL PIVOT MULTIPLE COLUMNS
- SQL “select where not in subquery” returns no results
- Equivalent of Oracle’s RowID in SQL Server
- Is it possible to specify condition in Count()?
- SQL Server dynamic PIVOT query?
- How to write a foreach in SQL Server?
- Error converting data type varchar
- Temporary table in SQL server causing ‘ There is already an object named’ error
- I want to use CASE statement to update some records in sql server 2005
- How to calculate age (in years) based on Date of Birth and getDate()
- ORDER BY items must appear in the select list if SELECT DISTINCT is specified
- Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot
- How can I group by date time column without taking time into consideration
- “select * into table” Will it work for inserting data into existing table
- SUM OVER PARTITION BY
- Update query using Subquery in Sql Server
- Varchar invalid for Sum operator
- LIKE vs CONTAINS on SQL Server
- Case in Select Statement
- Inserting multiple rows in a single SQL query? [duplicate]
- Conversion failed when converting date and/or time from character string while inserting datetime
- NOT IN vs NOT EXISTS
- Must declare the scalar variable
- Must declare the scalar variable
- INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
- INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
- What is the use of GO in SQL Server Management Studio & Transact SQL?
- How to rename a table in SQL Server?
- Drop a temporary table if it exists
- How do I perform an IF…THEN in an SQL SELECT?
- Each GROUP BY expression must contain at least one column that is not an outer reference
- SQL Server : Arithmetic overflow error converting expression to data type int
- Update statement to update multiple rows
- How to calculate percentage with a SQL statement
- Can a foreign key be NULL and/or duplicate?
- Why does NULL = NULL evaluate to false in SQL server
- Difference between JOIN and INNER JOIN
- Best way to do nested case statement logic in SQL Server
- “CASE” statement within “WHERE” clause in SQL Server 2008
- Get day of week in SQL Server 2005/2008
- Query error with ambiguous column name in SQL
- Check if table exists in SQL Server
- Operand type clash: int is incompatible with date + The INSERT statement conflicted with the FOREIGN KEY constraint
- Keeping it simple and how to do multiple CTE in a query
- How can I truncate a datetime in SQL Server?
- Difference between numeric, float and decimal in SQL Server
- How to group by month from Date field using sql
- How can I get column names from a table in SQL Server?
- SQL Server reports ‘Invalid column name’, but the column is present and the query works through management studio
- SQL Server String or binary data would be truncated
- How do I query for all dates greater than a certain date in SQL Server?
- Add a column with a default value to an existing table in SQL Server
- Remote table-Valued Function Calls are not allowed
- MS SQL compare dates?
- The backend version is not supported to design database diagrams or tables
- Determine ROW that caused “unexpected end of file” error in BULK INSERT?
- Determine ROW that caused “unexpected end of file” error in BULK INSERT?
- Get size of all tables in database
- Why is SQL server throwing this error: Cannot insert the value NULL into column ‘id’?
- Bulk load data conversion error (truncation)
- How to format a numeric column as phone number in SQL
- ListAGG in SQLSERVER
- How do I format a number with commas in T-SQL?
- Format number as percent in MS SQL Server
- Can I use multiple “with”?
- ‘CREATE PROCEDURE’ must be the only statement in the batch (Erro)
- Errors: “INSERT EXEC statement cannot be nested.” and “Cannot use the ROLLBACK statement within an INSERT-EXEC statement.” How to solve this?
- Incorrect syntax near ”
- To add server using sp_addlinkedserver
- Solutions for INSERT OR UPDATE on SQL Server
- Arithmetic overflow error converting varchar to data type numeric. ’10’ <= 9.00
- Append table to an existing one: SQL Server
- SQL Server – An expression of non-boolean type specified in a context where a condition is expected, near ‘RETURN’
- INSERT VALUES WHERE NOT EXISTS
- CASE IN statement with multiple values
- Pad a string with leading zeros so it’s 3 characters long in SQL Server 2008